>>>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 problem is that THEN should be an value, so it can not be a condition.
You may re-write it as
CASE WHEN SUM(Rec_Qty) IS NULL then SUM(Item_Qant) ELSE SUM(Item_Quant) - SUM(Rec_Qty) END > 0
This is to translate your condition exactly.
If it's not broken, fix it until it is.
My Blog