>>Suppose I have the following SQL Select:
>>
>>
>>select SUM(FIELD1) AS FIELD1, SUM(PODET.ITEM_QUANT) AS ITEM_QUANT, SUM(POREC.REC_QTY) AS REC_QTY, POREC.PODET_PK FROM POREC RIGHT
>>JOIN PODET ON PODET.PODET_PK = POREC.PODET_PK
>>GROUP BY PODET.PODET_PK
>>HAVING SUM(POREC.REC_QTY) < SUM(Podet.ITEM_QUANT) > 0
>>
>>
>>What if all values of REC_QTY are NULL, what would be the SUM( REC_QTY )? In my test it works, that is, the SQL creates a query. But I am not sure if it is accurate.
>>
>>I tried to change the HAVING to something like this:
>>
>>
>>HAVING CASE SUM(POREC.REC_QTY) IS NULL THEN SUM(PODET.ITEM_QUANT)>0 ELSE
>>SUM(POREC.REC_QTY < SUM( PODET.ITEM_QUANT) END
>>
>>
>>But I get error. I suppose I can't use IS NULL with SUM(), Correct?
>
>NULLs are excluded by the aggregate functions from calculations. So, if you have NULL values, then they will be not included into results. If all records have NULL values, then the result of aggregation is NULL.
>
>If you want to use 0 instead of NULL, use
>
>SUM(coalesce(myField,0))
Thank you. Can you see what is wrong with my HAVING with CASE? Why do I get error "near IS"?
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
