Re: SQL Challenge
Well, it can be done, of course.
It requires a stored procedure to have been created prior to using the
SELECT statement, but theat is within the scope of the rules -- the SPis created once when the DB is created.
The rest of the solution only breaks all the syntax diagrams in all the
manuals, but otherwise seems to work.
Try this sample coding with dbaccess on a 5.00 or later database.
===========================================================================
create temp table t2 (sa_id int, year int, period int, sale_value int);
create temp table t1 (cust_num int, sa_id int);
create temp table t3 (sa_id int, year int, period int, return_value int);
create procedure non_null_value(v1 integer, v2 integer default 0)
returning integer;
define rv integer;
if v1 is not null
then let rv = v1;
else let rv = v2;
end if
return rv;
end procedure;
execute procedure non_null_value(23);
execute procedure non_null_value(null, 10);
execute procedure non_null_value((select sum(tabid) from systables where tabid < 0));
insert into t1 values(10, 11);
insert into t1 values(10, 12);
insert into t1 values(10, 13);
insert into t2 values(11, 1995, 2, 1100);
insert into t2 values(12, 1995, 2, 1200);
insert into t3 values(11, 1995, 2, 1050);
insert into t3 values(13, 1995, 2, 1050);
insert into t1 values(20, 21);
insert into t1 values(20, 22);
insert into t2 values(21, 1995, 2, 2100);
insert into t2 values(22, 1995, 2, 2200);
insert into t1 values(30, 31);
insert into t1 values(30, 32);
insert into t3 values(31, 1995, 2, 3050);
insert into t3 values(32, 1995, 2, 3050);
SELECT t1.cust_num, t1.sa_id,
non_null_value((SELECT SUM(sale_value)
FROM t2
WHERE t1.sa_id = t2.sa_id AND t2.YEAR = 1995 AND t2.period = 2
)) -
non_null_value((SELECT SUM(return_value)
FROM t3
WHERE t1.sa_id = t3.sa_id AND t3.YEAR = 1995 AND t3.period = 2
)) net_sales
FROM t1;
===========================================================================
I don't have to explain -- it is a non-documented solution:-) You get
multiple rows per customer even if you omit the sa_id column from the
SELECT. I just put in the sa_id to explain why, and it helps with the
checking too.
cust_num sa_id net_sales
10 11 50
10 12 1200
10 13 -1050
20 21 2100
20 22 2200
30 31 -3050
30 32 -3050
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
>Date: Wed, 21 Jun 1995 17:50:00 -0400
>From: Scott.Allred@nsc.sprint.com
>X-Informix-List-Id: <list.6675>
>
> THE ULTIMATE SQL CHALLENGE
> --------------------------
>
> I need to be able to perform an aggregate function on two columns where the
> value of one column may be NULL. I am aware that Informix's definition of an
> aggregate that includes a NULL is NULL, but does anyone know of a way to
> represent NULL as ZERO so that the aggregate function will work.
>
> Example:
>
> select T1.cust_num, (T2.sale_value - T3.return value) net_sales
> from sa T1, OUTER sa_sale T2, OUTER sa_return T3
> where T1.sa_id = T2.sa_id
> and T1.sa_id = T3.sa_id
> and T2.year = 1995
> and T2.period = 2
> and T3.year = 1995
> and T3.period = 2>
> Sale value or return value could be NULL because customer X may have purchased
> something but never returned anything. The opposite is also true, for a given
> time period (i.e. February) customer X may have returned something but never
> purchased anything.
>
> I will throw in one more kicker. I need to be able to accomplish this with a
> single SELECT statement. That means you cannot use a temp table or view in an
> intermediate step to get the results I am after.
>
> The reason for the last little twist has to do with our application. We are
> writing a client/server Sales Analysis application in Visual Basic. We are
> using OpenLink's ODBC driver to get to the Informix Online DB residing on the
> server. We have already tried to send multiple SQL statements (i.e. create
> view A ...;, create view B ...;, select * from A, B ...; drop view A; drop
> view B;) to Informix, through ODBC without any success.
>
> I am looking for some documented or undocumented feature like MS Access's
> Inline If (IIF) which can be embedded into a SQL statement that would allow
> you to check for a NULL and convert it to a zero. I believe you can also do
> this in MS SQL Server.