diffrence between decode and case

[trysenthil]

hi all., i want to know the diffrence between the decode and case function that is available in oracle....help me ya...

[debasisdas]

By using either DECODE and CASE we can get the same output.
Also both can be used directly in SQL statments but performance wise CASE is faster in comparision to DECODE.

[Saii]

CASE can handle range operations also.

[wani]

CASE can handle range operations also.

decode is used for strings & case for numerics

[amitpatel66]

CASE can be used in SELECT statement as well as PLSQL Block

DECODE can be used only in SELECT statement.

[laconicamit]

I found we cannot use CASE in PL/SQL.
We can use DECODE in PL/SQL.

[amitpatel66]

I found we cannot use CASE in PL/SQL.
We can use DECODE in PL/SQL.

CASE in PLSQL: Works Fine

DECLARE
CURSOR C1 IS SELECT * FROM emp;
BEGIN
FOR I IN C1 LOOP
IF(C1%FOUND) THEN
CASE (I.empno)
WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('MANAGER');
ELSE
DBMS_OUTPUT.PUT_LINE('EMPLOYEE');
END CASE;
END IF;
END LOOP;
END;
 

CASE in SQL: Works fine

SELECT CASE WHEN empno = 1 THEN 'MANAGER' ELSE 'EMPLOYEE' END from emp
 

DECODE in PLSQL: Errors out

 
SQL> DECLARE
  2  CURSOR C1 IS SELECT * FROM emp;
  3  emp_type VARCHAR2(20);
  4  BEGIN
  5  FOR I IN C1 LOOP
  6  IF(C1%FOUND) THEN
  7  emp_type:= DECODE(I.empno,1,'MANAGER','EMPLOYEE');
  8  DBMS_OUTPUT.PUT_LINE(emp_type);
  9  END IF;
 10  END LOOP;
 11  END;
 12  /
DECLARE
*
ERROR at line 1:
ORA-06550: line 7, column 12:
 PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored
 

DECODE in SQL: Works fine

SELECT DECODE(empno,1,'MANAGER','EMPLOYEE') from emp
 

Clear??

[laconicamit]

DECODE in PLSQL: Errors out

 
SQL> DECLARE
  2  CURSOR C1 IS SELECT * FROM emp;
  3  emp_type VARCHAR2(20);
  4  BEGIN
  5  FOR I IN C1 LOOP
  6  IF(C1%FOUND) THEN
  7  emp_type:= DECODE(I.empno,1,'MANAGER','EMPLOYEE');
  8  DBMS_OUTPUT.PUT_LINE(emp_type);
  9  END IF;
 10  END LOOP;
 11  END;
 12  /
DECLARE
*
ERROR at line 1:
ORA-06550: line 7, column 12:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored
 

Clear??

DECODE in PLSQL: "Works Fine"

set serveroutput on;
DECLARE
emp_comm NUMBER;
BEGIN
SELECT DECODE(comm,NULL,0) INTO emp_comm FROM emp WHERE empno=7900;
DBMS_OUTPUT.PUT_LINE(emp_comm);
END;
/

Please read the error message carefully, before you HOP to give a "Verdict"

CASE in PL/SQL: Absolutely Working Fine. (9i,10g,11g)
But It is introduced in 9i or may be in Oracle 8i with some patches, I guess.. The use of CASE in Pl/Sql was not supported in 8i.

[amitpatel66]

DECODE in PLSQL: "Works Fine"

set serveroutput on;
DECLARE
emp_comm NUMBER;
BEGIN
SELECT DECODE(comm,NULL,0) INTO emp_comm FROM emp WHERE empno=7900;
DBMS_OUTPUT.PUT_LINE(emp_comm);
END;
/

Please read the error message carefully, before you HOP to give a "Verdict"

CASE in PL/SQL: Absolutely Working Fine. (9i,10g,11g)
But It is introduced in 9i or may be in Oracle 8i with some patches, I guess.. The use of CASE in Pl/Sql was not supported in 8i.

The solutions that I provided is from Oracle 9i version and not oracle 8i.
come on wake up and check out the latest versions that oracle has released 9i,10g, 11g and still you talking about 8i??

[amitpatel66]

DECODE in PLSQL: "Works Fine"

set serveroutput on;
DECLARE
emp_comm NUMBER;
BEGIN
SELECT DECODE(comm,NULL,0) INTO emp_comm FROM emp WHERE empno=7900;
DBMS_OUTPUT.PUT_LINE(emp_comm);
END;
/

Please read the error message carefully, before you HOP to give a "Verdict"

CASE in PL/SQL: Absolutely Working Fine. (9i,10g,11g)
But It is introduced in 9i or may be in Oracle 8i with some patches, I guess.. The use of CASE in Pl/Sql was not supported in 8i.

And the solution that you provided, you are using a "SQL" statement within a PLSQL BLOCK. SO DECODE works fine with SQL and not in assignment statement in PLSQL BLOCK.

[laconicamit]

The solutions that I provided is from Oracle 9i version and not oracle 8i.
come on wake up and check out the latest versions that oracle has released 9i,10g, 11g and still you talking about 8i??

What happened to you Dear!! Why you are acting as Loser!!!

I wrote "Absolutely working Fine" can't you read that. I said I checked it in 9i/10g/11g. It is working beautifully fine.

One should be aware of all the releases (What I think, Don't know about you)
There is nothing to "Wake Up" and "HOPPING" into new release when you don't know the basics [:D]

[amitpatel66]

I found we cannot use CASE in PL/SQL.
We can use DECODE in PL/SQL.

Both of your above statements are wrong!!

I think you are still in to basics of oracle, come on wake up and check out later versions of oracle 9i,10g and 11g which supports CASE in PLSQL block as well.

GOOD that you had put some 2% of effort to atleast check about CASE in versions 9i,10g,11g.

DECODE - The example that you have given in your later posts, you have used DECODE in SELECT statement with in PLSQL block, so DECODE can be used only with SELECT statement and not in assignment statement.

DONT comment on anything blindly and then change your words.
At one point of time you say CASE CANNOT BE USED in PLSQL BLOCK, then you come and say it can be used in higher versions.

YOU are here to provide some positive solutions or atleast an idea for the user to proceed further.

I think Senthil would have got his answer by now so I am not gonna explain any thing further to anybody!!

[laconicamit]

Both of your above statements are wrong!!

I think you are still in to basics of oracle, come on wake up and check out later versions of oracle 9i,10g and 11g which supports CASE in PLSQL block as well.

GOOD that you had put some 2% of effort to atleast check about CASE in versions 9i,10g,11g.

DECODE - The example that you have given in your later posts, you have used DECODE in SELECT statement with in PLSQL block, so DECODE can be used only with SELECT statement and not in assignment statement.

DONT comment on anything blindly and then change your words.
At one point of time you say CASE CANNOT BE USED in PLSQL BLOCK, then you come and say it can be used in higher versions.

YOU are here to provide some positive solutions or atleast an idea for the user to proceed further.

I think Senthil would have got his answer by now so I am not gonna explain any thing further to anybody!!

Thank you!!

Many thanks for your advice.