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 | +--------------------------------------------------------------------+