NVL using case in query

Asked by At

We have a query that uses case in NVL statement However I think NVL can be removed

Please refer to the part of the query below -

select f1, NVL((
        CASE
            WHEN PROD_CD = 'ESALE'
            THEN SUM (prod_count)
            WHEN PROD_CD = 'RSALE'
            THEN (SUM (prod_count) * -1)
            ELSE NULL
        END),0) AS SALEQTY

Can we also avoid NVL by writing like this

select f1, 
        CASE
            WHEN PROD_CD = 'ESALE'
            THEN SUM (prod_count)
            WHEN PROD_CD = 'RSALE'
            THEN (SUM (prod_count) * -1)
            ELSE 0
        END AS SALEQTY,

2 Answers

1
Gordon Linoff On Best Solutions

First, this is an odd construct. Second, I prefer COALESCE() (the standard function) to NVL() -- but that is irrelevant.

You need the NVL()/COALESCE() if all prod_count values can be NULL for a given group. If that is the case, then you need the outer check. Merely adding an ELSE on a different column doesn't help.

I'll also note that the logic looks suspicious. Normally the CASE goes as the argument to SUM() -- and a NULL-check is usually not necessary:

SELECT f1, 
       SUM(CASE WHEN PROD_CD = 'ESALE'
                THEN prod_count
                WHEN PROD_CD = 'RSALE'
                THEN - prod_count
                ELSE 0
            END) AS SALEQTY,
1
TineO On

NVL check the first argument, and if that argument is null, then it replaces it with the second argument. So yes, you absolutely can remove it, I have no idea who the hell did this as its completely pointless.

Maybe if the NVL was first and someone added the case statement later and didnt want to mess with the current code. Since I dont think there is really a difference in performance, it doesent hurt if it stays, it also doesent make it much more difficult to understand, so it can stay, but there is no reason for it to stay.