Standard SQL alternative to Oracle DECODE

Question

Is there an ANSI SQL equivalent to Oracle's DECODE function?

Oracle's decode function is the IF-THEN-ELSE construct in SQL.

score 19 · Accepted Answer · answered Jul 09 '10 at 11:23

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

score 3 · Answer 2 · answered Oct 09 '18 at 09:13

3

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;

answered Oct 09 '18 at 09:13

Alexei

Note that `case when is null then` has the expected behaviour whereas `case when null then` doesn't, it's a famous pitfall. – gouessej Apr 18 '19 at 11:18

score 2 · Answer 3 · edited May 23 '17 at 11:48

2

CASE WHEN a=1 THEN value1
     WHEN a=2 THEN value2
     ELSE default
END

edited May 23 '17 at 11:48

Community

answered Jul 09 '10 at 11:18

Rup

score 0 · Answer 4 · answered Oct 09 '18 at 11:23

0

adding it up null can be compared in decode whereas in case it is not.

answered Oct 09 '18 at 11:23

nikhil sugandh

4 Answers4