SQL CHALLENGE
S> 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
n
> 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.
Actually, That is NOT the way Informix (or other RDBMSs) treat NULLs.
aggregate functions discard NULL values. An aggregate function is a
function like SUM or AVG.
Given the values 2, NULL, 4 SUM would return 6, and AVG would return 3
(the average of the rows considered--the null is discarded).
S> Example:
S> 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
That isn't actually an aggregate, but a computed column.
S> Sale value or return value could be NULL because customer X may have purcha
ed
> something but never returned anything. The opposite is also true, for a gi
en
> time period (i.e. February) customer X may have returned something but neve
> purchased anything.
S> 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.
[reasons for the kicker deleted]
S> 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 d
> this in MS SQL Server.
I think UNION is documented. This can be done in a single select, but it
isn't pretty. Try the following:
select T1.cust_num, (T2.sale_value - T3.return value) net_sales
from sa T1, sa_sale T2, 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
UNION
select T1.cust_num, T2.sale_value net_sales
from sa T1, sa_sale T2
where T1.sa_id = T2.sa_id
and T2.year = 1995
and T2.period = 2
and T1.sa_id NOT IN (select T1.sa_id
from sa T1, sa_sale T2, 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)
UNION
select T1.cust_num, (-1 * T3.return value) net_sales
from sa T1, sa_return T3
where T1.sa_id = T3.sa_id
and T3.year = 1995
and T3.period = 2
and T1.sa_id NOT IN (select T1.sa_id
from sa T1, sa_sale T2, 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)
I made some assumptions about your setup which may or may not be valid. You
may need a similar query with a correlated subselect using NOT EXISTS
instead of NOT IN, depending on your keys etc.
S> I am very interested in any and all suggestions.
Make sure you have candles--the query should work, but it may dim the
lights :).
-- SPEED 1.40 [NR]: Evaluation day 91...