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 It occurs to me that the problem you are having is that you are selecting all customers - even those who do not have any activity. For these of course the results are going to be null. If your intent is to perform some form of aggregate, then the customer number is going to drop out anyway, ergo why the OUTER? If you remove that then you remove the problem. cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, BSMC Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ It ain't over till the digestively challenged lady sings. _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________