Are a Case Statement and a Decode Equivalent

Are a CASE statement and a DECODE equivalent?

Ben has written a lengthy answer on the differences between DECODE and CASE. He demonstrates that DECODE and CASE may return different datatypes for apparently the same set of values without properly explaining why this happens.

DECODE() is quite prescriptive: it is always the datatype of the first result parameter. Oracle applies implicit conversion to all the other result parameters. It will throw an error , if (say) the first result parameter is numeric and the default value is a date.

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

This is described in the documentation: find out more.

In the first scenario the first result parameter is NULL, which Oracle decides to treat as VARCHAR2. If we change it so that the first result parameter is numeric and the default value is null the DECODE() statement will return a NUMBER; a DUMP() proves that this is so.

Whereas CASE insists that all the returned values have the same datatype, and will throw a compilation error if this is not the case. It won't apply implicit conversion. This is also covered in the documentation. Read it here.

The difference boils down to this. The following DECODE statement will run, the CASE statement won't:

select decode(1, 1, 1, '1') from dual;

select case 1 when 1 then 1 else '1' end from dual;

Obligatory SQL Fiddle.

Converting a nested decode into equivalent CASE statement (needed for conversion from Oracle to PostgreSQL)

That would be something like this:

SELECT CASE
          WHEN NVL (tab1.flag1, '~') = 'D'
          THEN
             CASE
                WHEN tab1.code_oid = NVL (tab3.bu_id, '-') THEN 1
                ELSE 0
             END
          WHEN NVL (tab1.flag1, '~') = 'C'
          THEN
             CASE
                WHEN tab1.code_oid = NVL (tab3.cost_id, '-') THEN 1
                ELSE 0
             END
          ELSE
             CASE
                WHEN tab2.oid =
                        CASE
                           WHEN tab1.co_id IS NULL THEN tab2.oid
                           ELSE tab1.co_id
                        END
                THEN
                   1
                ELSE
                   0
             END
       END
  INTO v
  FROM ...

Note that your FROM clause contains 3 tables, but WHERE joins just two of them. What about tab3? Also, consider switching to ANSI join.

CASE vs. DECODE

As always with Oracle ... AskTom...

From this post...

Decode is somewhat obscure -- CASE is
very very clear. Things that are
easy to do in decode are easy to do in
CASE, things that are hard or near
impossible to do with decode are easy
to do in CASE. CASE, logic wise, wins
hands down.

From a performance point of view seems they are about the same, again above article mentions some speed differences but without benchmarking the particular statements it's hard to say.

Oracle: CASE equivalent of DECODE

SELECT E1.last_name Boss,
    E2.last_name Subordinate
FROM Employee E1 FULL JOIN Employee E2
    ON E1.id = E2.boss_id
WHERE
    (CASE
    WHEN E1.ID is null THEN 'M' ELSE E1.sex END) = 'M' 
    AND
    (CASE 
    WHEN E2.ID is null THEN 'M' ELSE E2.sex END) = 'M';

When comparing a value to null, use IS NULL and IS NOT NULL .

You can read about it here.

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'
         END

2) Searched CASE expression:

CASE WHEN col < 0 THEN 'Negative'
     WHEN col = 0 THEN 'Zero'
     ELSE 'Positive'
     END

Which is better for PL/SQL, IF-ELSE OR SELECT DECODE IN ORACLE

In Oracle PLSQL block , 2 types of engines works. First SQL engine and another PLSQL engine. Whenever you write a SQL statement in a PLSQL block, the switching of engine takes place and this phenomena is called Context Switching. The more context switching the less performant application would be.

When you do :

if var1 = 'a' then
 var2 := 'x';

elseif var1 = 'b' then
 var2 := 'y';

else
 var2 := 'z';

end if;

The statement is evaluated in PLSQL engine and no context switching occurs. But when you do :

begin
select decode(var1,'a','x','b','y','z') into var2 from dual;
end;

PLSQL engine changes the control to SQL engine and context switching takes places. So this operation would make less performant.

SQL order by DECODE and CASE return different data for the same conditions

I asked what the datatype was because sorting looked like sorting strings, not numbers.

Have a look at this. First, only one expression in order by clause:

SQL> with test (seq_diff) as
  2    (select -990 from dual union all
  3     select -610 from dual union all
  4     select -1350 from dual union all
  5     select -1340 from dual
  6    )
  7  select *
  8  from test
  9  order by decode(seq_diff, abs(seq_diff), seq_diff, null);

  SEQ_DIFF
----------
      -990
     -1340
     -1350
      -610

SQL>

How are they sorted? They aren't. According to sample data set, no seq_diff is equal to abs(seq_diff) so ordering moves to null which causes "randomly" ordered values. They aren't sorted at all.

---

Now, let's add another decode into order by:

SQL> with test (seq_diff) as
  2    (select -990 from dual union all
  3     select -610 from dual union all
  4     select -1350 from dual union all
  5     select -1340 from dual
  6    )
  7  select *
  8  from test
  9  order by decode(seq_diff, abs(seq_diff), seq_diff, null),
 10           decode(seq_diff, abs(seq_diff), null, seq_diff) desc;

  SEQ_DIFF
----------
      -990
      -610
     -1350
     -1340

SQL>

The first decode didn't do anything, as if it doesn't exist so we move on to the second decode. Again, according to data set, no seq_diff is equal to abs(seq_diff), but this time it returns seq_diff. Documentation (as @krokodilko mentioned in their comment) says:

• The DECODE function returns a value that is the same datatype as the first result in the list.
• If the first result is NULL, then the return value is converted to VARCHAR2.
  
  
  • this is our case, so return value (seq_diff) is converted to varchar2
• If the first result has a datatype of CHAR, then the return value is converted to VARCHAR2.
• If no matches are found, the default value is returned.
• If default is omitted and no matches are found, then NULL is returned.

Once again: our case is the second one:

decode(seq_diff, abs(seq_diff), null, seq_diff)
                                ----
                                the first result is NULL

Therefore, seq_diff is converted to a string and values are sorted as such. Let's check that:

SQL> with test (seq_diff) as
  2    (select -990 from dual union all
  3     select -610 from dual union all
  4     select -1350 from dual union all
  5     select -1340 from dual
  6    )
  7  select *
  8  from test
  9  order by to_char(seq_diff) desc;

  SEQ_DIFF
----------
      -990
      -610
     -1350
     -1340

SQL>

See? The same result as we got with order by decode(seq_diff, abs(seq_diff), null, seq_diff) desc;

---

The final part of your order by clause is trivial (abs(dist_diff)), I guess there's no need to explain that.

---

That's why you got strange result with DECODE; actually, that's expected behavior.

Need help in using decode or case function

You can try something like:

   Select CASE WHEN (t.c1 is null or t.c1 >Sysdate) THEN 'ACTIVE' ELSE 'INACTIVE' end 
   from t;   

---

Related Topics