I have a table with 3 columns: c1, c2 and c3. I want to write a select statement so that if both c1 and c2 are null, display c3, otherwise display c1.
What would be the best way to write a select statement in the above scenario?
1 Answer
Using CASE
CASE WHEN C1 IS NULL AND C2 IS NULL
THEN C3
ELSE C1
END
Using NVL2
NVL2(C1,C1,NVL2(C2,C1,C3))
OR
NVL2(C1||C2,C1,C3)
Using DECODE
DECODE(C1,NULL,DECODE(C2,NULL,C3,C1),C1)
Readability and ease wise CASE wins.
answered Feb 23, 2014 at 8:29
Maheswaran Ravisankar
17.9k6 gold badges50 silver badges69 bronze badges
7 Comments
user1664398
One of my colleagues had written like this. Is this a good way to handle this scenario? select nvl2(c1||c2, c1, c3) from XXX
Maheswaran Ravisankar
No it is wrong, if either c1 or c2 is null, c3 is returned in that expression, since concatenating any string with NULL gives NULL only, but we should always handle NULL before hand.As you dont know, which one was NULl, either c1 or c2.. also CASE would be everyone's choice.
user1664398
I've tried my colleague's select statement in Oracle 11g and found that concating null string with another string literal will return the literal itself. So my colleague's solution works even though I also feel that "Case When" is a better solution.
dnoeth
Oracle is known to have a buggy NULL vs. '' (empty string) implementation. It treats both the same, usually but not always like NULL, e.g. TRANSLATE('jhkhkxhkx', 'x', '') will not work, you need TRANSLATE('jhkhkxhkx', '.x', '.') instead
Ben
It's not buggy, it's intended @dnoeth and the behaviour of TRANSLATE() is well documented, so I don't see how you can call this buggy either.
|