Standard SQL alternative to Oracle DECODE
15,345
Solution 1
A CASE expression is the ANSI SQL method, of which there are 2 varieties, "simple" and "searched":
1) Simple CASE expression:
CASE col WHEN 1 THEN 'One'
WHEN 2 THEN 'Two'
ELSE 'More'
END
2) Searched CASE expression:
CASE WHEN col < 0 THEN 'Negative'
WHEN col = 0 THEN 'Zero'
ELSE 'Positive'
END
Solution 2
Please note that Oracle DECODE treats null as equal to null, while CASE(and any other comparisons) don't.
Example: Decode(a,b,1,0) will return 1 if both a and b are nulls.
Just run these 2 statements to see the difference.
select case null when null then 'Y' else 'N' end dd from dual;
select decode(null, null, 'Y', 'N') dd from dual;
Solution 3
CASE WHEN a=1 THEN value1
WHEN a=2 THEN value2
ELSE default
END
Author by
JavaRocky
Software Engineer. "It the code is hot, we gonna kill it! :)" -- DaCav5 Geekstyle
Updated on June 12, 2022Comments
-
JavaRocky about 2 years
Is there an ANSI SQL equivalent to Oracle's DECODE function?
Oracle's decode function is the IF-THEN-ELSE construct in SQL.
-
gouessej about 5 yearsNote that
case when <whatever> is null then
has the expected behaviour whereascase <whatever> when null then
doesn't, it's a famous pitfall.