Home » SQL & PL/SQL » SQL & PL/SQL » difference between decode Vs case...when
icon4.gif  difference between decode Vs case...when [message #111428] Wed, 16 March 2005 06:22 Go to next message
vinothn
Messages: 8
Registered: March 2005
Location: chennai
Junior Member
Hi,

pls give the difference between decode Vs case...when with example.

tks
vinoth
Re: difference between decode Vs case...when [message #111631 is a reply to message #111428] Fri, 18 March 2005 05:56 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
The difference is in the syntax, standards compliance and ease of use. Here are 2 simple examples:

SQL> SELECT ename,
  2         DECODE(deptno, 10, 'ACCOUNTING',
  3                        20, 'RESEARCH',
  4                        30, 'SALES',
  5                        40, 'OPERATIONS',
  6                            'UNKNOWN') AS department
  7    FROM emp
  8   WHERE rownum < 4
  9  /

ENAME      DEPARTMENT
---------- ----------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES


SQL> SELECT ename,
  2         CASE deptno
  3           WHEN 10 THEN 'ACCOUNTING'
  4           WHEN 20 THEN 'RESEARCH'
  5           WHEN 30 THEN 'SALES'
  6           WHEN 40 THEN 'OPERATIONS'
  7           ELSE
  8                        'UNKNOWN'
  9         END AS department
 10    FROM emp
 11   WHERE rownum < 4
 12  /

ENAME      DEPARTMENT
---------- ----------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES


For more info, please refer to the Oracle SQL Reference Guide.

Best regards.

Frank
Re: difference between decode Vs case...when [message #111634 is a reply to message #111631] Fri, 18 March 2005 06:00 Go to previous messageGo to next message
vinothn
Messages: 8
Registered: March 2005
Location: chennai
Junior Member
hi frank,

i am asking about functional difference not syntax difference.

urs
vinoth

Re: difference between decode Vs case...when [message #111647 is a reply to message #111634] Fri, 18 March 2005 06:56 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
DECODE works with expressions which are scalar values.

CASE can work with predicates and subqueries in searchable form:

SQL> select ename,
2 case
3 when ename in ('KING','SMITH','ALLEN') then
4 'Managers'
5 when exists (select 1 from dept where deptno = emp.deptno and deptno = 10) then
6 'Guy from 10th'
7 else
8 'Another person'
9 end blah_blah
10 from emp
11 /

ENAME BLAH_BLAH
---------- --------------
SMITH Managers
ALLEN Managers
WARD Another person
JONES Another person
MARTIN Another person
BLAKE Another person
CLARK Guy from 10th
SCOTT Another person
KING Managers
TURNER Another person
ADAMS Another person

ENAME BLAH_BLAH
---------- --------------
JAMES Another person
FORD Another person
MILLER Guy from 10th

14 rows selected.


Rgds.
Re: difference between decode Vs case...when [message #111650 is a reply to message #111647] Fri, 18 March 2005 07:03 Go to previous messageGo to next message
vinothn
Messages: 8
Registered: March 2005
Location: chennai
Junior Member
hi frank,niki

fine, now i got one more difference decode we cant use for range(like 2000 to 4000 etc.). but case we can.

tks
vinoth


Re: difference between decode Vs case...when [message #111668 is a reply to message #111428] Fri, 18 March 2005 09:21 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Oracle also boasts that CASE executes faster in the optimizer than does DECODE. They claim in any instance you can use DECODE, you could CASE instead and the performance will improve. I am not sure if this is 100% correct, but it seems to be a fair estimation in my experience.

HTH,
Steve
Re: difference between decode Vs case...when [message #137753 is a reply to message #111668] Fri, 16 September 2005 09:57 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Just found out the hard way that CASE handles NULL values differently.

DECODE:
SQL> select decode(NULL
  2               ,NULL,'NULL'
  3               ,'NOT NULL'
  4               ) x
  5    from dual
  6  /

X
----
NULL


CASE:
SQL> SELECT CASE NULL
  2         WHEN NULL THEN
  3           'NULL'
  4         ELSE
  5           'ELSE'
  6         END x
  7    FROM dual
  8  /

X
----
ELSE


We had a nasty little bug in one of our programs due to this.

MHE
Re: difference between decode Vs case...when [message #137762 is a reply to message #137753] Fri, 16 September 2005 10:44 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Yep, you need to handle it with a searched case expression as
"case expr when " works on equality (i.e. when null = null)

Quote:

SELECT CASE
WHEN null IS NULL THEN
'NULL'
ELSE
'ELSE'
END x
FROM dual



HTH
Jim
Re: difference between decode Vs case...when [message #137825 is a reply to message #111428] Sat, 17 September 2005 03:50 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

There is one more Important difference between CASE and DECODE

DECODE can be used Only inside SQL statement....
But CASE can be used any where even as a paramtre of a function/procedure

Eg:-

SQL> create or replace procedure pro_01(n number) is
  2  begin 
  3  dbms_output.put_line(' The number  = '||n);
  4  End;
  5  /

Procedure created.

SQL> set serverout on
SQL> var a varchar2(5);
SQL> Begin
  2  :a := 'ONE';
  3  End;
  4  /

PL/SQL procedure successfully completed.

SQL> Begin
  2   pro_01(Decode(:a,'ONE',1,0));
  3  End;
  4  /
 pro_01(Decode(:a,'ONE',1,0));
        *
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL
statement only
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored


SQL> Begin
  2    pro_01(case :a when 'ONE' then 1 else 0 end);
  3  End;
  4  /
The number  = 1

PL/SQL procedure successfully completed.

SQL> 


Thumbs Up
Rajuvan.
Re: difference between decode Vs case...when [message #302661 is a reply to message #111650] Tue, 26 February 2008 08:52 Go to previous messageGo to next message
panda.ranjit
Messages: 2
Registered: September 2007
Location: Mumbai
Junior Member
can u write code for it.
thanks in advance.
thanks,
ranjit
Re: difference between decode Vs case...when [message #302665 is a reply to message #302661] Tue, 26 February 2008 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For what?

Regards
Michel
Re: difference between decode Vs case...when [message #302707 is a reply to message #302661] Tue, 26 February 2008 12:43 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
You are asking a question to a three year old question. Please start your own.
Previous Topic: CASE statement with Exists clause
Next Topic: PL/SQL combine two columns in one variable
Goto Forum:
  


Current Time: Fri Dec 09 11:26:02 CST 2016

Total time taken to generate the page: 0.13237 seconds