SQL Challenge
>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.
> I am very interested in any and all suggestions.
> Thanks,
> Scott
> +--------------------------------------------------------------------+
> | Scott Allred Internet: scott.allred@nsc.sprint.com |
> | Sprint/North Supply AOL: kujaahawk@aol.com |
> | New Century, KS Phone: (913) 791-7000 x1810 |
> | USA Fax: (913) 791-7671 |
> +--------------------------------------------------------------------+
You do not say what version of informix you are using. If it is V5 or later,
have you tried
using a Stored Procedure? I know this might not be possible because some
ODBC
drivers do not support their use, but you might be lucky.
In case you have not used procedures before, you write the equivalent of a
4GL
function which gets loaded into the engine. To use the procedure you use the
following syntax:
EXECUTE PROCEDURE proc_name(parnam....)
At leasat when used within 4GL, these procedures behave in the same way as a
SELECT CURSOR.
Mark Denham
BBC
London, UK
Mark.Denham@bbc.co.uk