Standard SQL alternative to Oracle DECODE
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' END2) Searched CASE expression:
CASE WHEN col < 0 THEN 'Negative' WHEN col = 0 THEN 'Zero' ELSE 'Positive' END
CASE WHEN a=1 THEN value1 WHEN a=2 THEN value2 ELSE defaultEND
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;