Case VS. Decode

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.

Which approach is better - CASE WHEN or DECODE

Recently I stumbled upon this: https://community.oracle.com/thread/1112467?tstart=0

"The performance difference is so slight that it makes very little sense in using that as primary criteria for whether to use CASE or DECODE. So unless you're calling this statement from a very tight loop doing millions of iterations, the decision should rather be which one, CASE or DECODE, best suits the need."

Oracle DECODE vs. CASE. Queries return different result sets

The difference comes from the difference in behavior when comparing to null:

SQL> select decode(null,null,1) from dual;

DECODE(NULL,NULL,1)
-------------------
                  1

SQL> select case null when null then 1 end from dual;

CASENULLWHENNULLTHEN1END
------------------------

The second query return NULL and not 1, so 'case x when null then 1 end' does NOT return 1 when x is NULL, while this works:

SQL> select case when null is null then 1 end from dual;

CASEWHENNULLISNULLTHEN1END
--------------------------
                         1

What is the difference between case when and decode?

Your code is invalid; should be something like this:

SELECT customer.customerid,
       customer.contractid,
       customer.termed,
       customer.bracnchcode,
       (CASE
           WHEN customer.contractid < 5000000000
           THEN
              'A'
           WHEN customer.contractid BETWEEN 5000000001 AND 100000000000
           THEN
              'B'
           ELSE
              'C'
        END) AS RANK
  FROM customer;

As of your question regarding DECODE: it would make things way more complex in this case because you'd have to calculate the difference between contractid and boundaries you set check sign of the difference, probably use nested decodes which then becomes difficult to read and even worse to understand. Stick to CASE.

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.

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.

What is exactly difference between a 'statement' and a 'function' in oracle sql?

In SQL, there is no such thing as a "CASE statement". Your example is a CASE expression. (CASE statements do exist in PL/SQL.)

The documentation states "An expression is a combination of one or more values, operators, and SQL functions that evaluates to a value." So a SQL function is not different from an expression, it is a specific type of expression.

Note that DECODE and CASE behave differently when comparing NULL values: DECODE considers two NULLs to be "the same", which is an exception to the rule that comparing a NULL to anything has an "unknown" result.

with data(a, b) as (
  select 1,1 from dual union all
  select 1,null from dual union all
  select null,1 from dual union all
  select null, null from dual
)
select a, b,
  decode(a,b,'same','different') decode_result,
  case when a = b then 'same' else 'different' end case_result
from data;

A      B      DECODE_RESULT CASE_RESULT
------ ------ ------------- -----------
1      1      same          same
1      (null) different     different
(null) 1      different     different
(null) (null) same          different

---

Related Topics