CODEWITHSUNDEEP
sqlhadoophivehiveql
Vidya K
Vidya K

Reputation: 103

NVL using case in query

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,

Upvotes: 1

Views: 5280

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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,

Upvotes: 1

TineO
TineO

Reputation: 1033

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.

Upvotes: 1

Related Questions