Good call Barbara- but even better, NVL() was meant for this...
>Matt,
>Summing NULL values doesn't work well < grin >. Why don't you surround the "SUM(b.Num2)" code with an isnull()
>SELECT SUM(a.num1) AS num1, SUM( iif(isnull(b.Num2), 0, b.Num2)) AS num2 ;
>........
>You might also specify the 'customer' field used in the ORDER and GROUP clauses as a.Customer to make certain there are no NULL values there either.
>
>HTH
>Barbara
>
>
>>I'm getting an error resulting from a SQL statement that worked fine yesterday, but for some reason not today.
>>
>>The statement is:
>>
>>SELECT SUM(a.num1) AS num1, SUM(b.Num2) AS num2 ;
>> FROM file1 A ;
>> LEFT OUTER JOIN ;
>> file2 B ;
>> ON a.customer = b.customer ;
>> ORDER BY customer ;
>> GROUP BY customer ;
>> INTO CURSOR flipper ;
>> NOFILTER
>>
>>The error reads field 'num2' does not affect NULL values.
>>
>>Of course this happens when table B doesn't have any matching records for the customer. I've tried SETting NULL ON and OFF - no affect.
>>
>>Are there any SQL gurus out there that could lend a hand?
>>
>>Thanks,
>>
>>-Matt
Erik Moore
Clientelligence