CODEWITHSUNDEEP
sqloracle-database
Skn
Skn

Reputation: 101

nullif not working

My nullif doesnt seem to be working when v2 is null, do I have this right?

SELECT ROUND (v1 / NULLIF (v2, 0), 3) FROM t1 WHERE id = 100

for eg: when V1=58 and V2 is null, the result should be 58 but its returning null.

Upvotes: 0

Views: 450

Answers (2)

user2031995
user2031995

Reputation:

Please try this:

SELECT ROUND (v1/ COALESCE(v2,1), 3)
FROM FROM t1 
WHERE id = 100;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270793

You are confusing NULLIF() and COALESCE(). Your expression is correct for preventing divide-by-zero. It is exactly the right thing to use in this situation, and it is working correctly.

If you want to treat a NULL value as 1, you could do something like this:

SELECT ROUND(v1 / (CASE WHEN v2 IS NULL THEN 1
                        WHEN v2 <> 0 THEN v2
                   END), 3)
FROM t1
WHERE id = 100;

That seems like odd logic to me.

Upvotes: 1

Related Questions