469,301 Members | 2,108 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,301 developers. It's quick & easy.

diffrence between decode and case

hi all., i want to know the diffrence between the decode and case function that is available in oracle....help me ya...
Sep 28 '07 #1
12 7432
debasisdas
8,127 Expert 4TB
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.
Sep 28 '07 #2
Saii
145 Expert 100+
CASE can handle range operations also.
Sep 28 '07 #3
wani
2
CASE can handle range operations also.
decode is used for strings & case for numerics
Oct 4 '07 #4
amitpatel66
2,367 Expert 2GB
CASE can be used in SELECT statement as well as PLSQL Block

DECODE can be used only in SELECT statement.
Oct 4 '07 #5
I found we cannot use CASE in PL/SQL.
We can use DECODE in PL/SQL.
Oct 4 '07 #6
amitpatel66
2,367 Expert 2GB
I found we cannot use CASE in PL/SQL.
We can use DECODE in PL/SQL.
CASE in PLSQL: Works Fine

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. CURSOR C1 IS SELECT * FROM emp;
  3. BEGIN
  4. FOR I IN C1 LOOP
  5. IF(C1%FOUND) THEN
  6. CASE (I.empno)
  7. WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('MANAGER');
  8. ELSE
  9. DBMS_OUTPUT.PUT_LINE('EMPLOYEE');
  10. END CASE;
  11. END IF;
  12. END LOOP;
  13. END;
  14.  
CASE in SQL: Works fine

Expand|Select|Wrap|Line Numbers
  1. SELECT CASE WHEN empno = 1 THEN 'MANAGER' ELSE 'EMPLOYEE' END from emp
  2.  
DECODE in PLSQL: Errors out
Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> DECLARE
  3.   2  CURSOR C1 IS SELECT * FROM emp;
  4.   3  emp_type VARCHAR2(20);
  5.   4  BEGIN
  6.   5  FOR I IN C1 LOOP
  7.   6  IF(C1%FOUND) THEN
  8.   7  emp_type:= DECODE(I.empno,1,'MANAGER','EMPLOYEE');
  9.   8  DBMS_OUTPUT.PUT_LINE(emp_type);
  10.   9  END IF;
  11.  10  END LOOP;
  12.  11  END;
  13.  12  /
  14. DECLARE
  15. *
  16. ERROR at line 1:
  17. ORA-06550: line 7, column 12:
  18. PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
  19. ORA-06550: line 7, column 1:
  20. PL/SQL: Statement ignored
  21.  
DECODE in SQL: Works fine
Expand|Select|Wrap|Line Numbers
  1. SELECT DECODE(empno,1,'MANAGER','EMPLOYEE') from emp
  2.  
Clear??
Oct 5 '07 #7
DECODE in PLSQL: Errors out
Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> DECLARE
  3.   2  CURSOR C1 IS SELECT * FROM emp;
  4.   3  emp_type VARCHAR2(20);
  5.   4  BEGIN
  6.   5  FOR I IN C1 LOOP
  7.   6  IF(C1%FOUND) THEN
  8.   7  emp_type:= DECODE(I.empno,1,'MANAGER','EMPLOYEE');
  9.   8  DBMS_OUTPUT.PUT_LINE(emp_type);
  10.   9  END IF;
  11.  10  END LOOP;
  12.  11  END;
  13.  12  /
  14. DECLARE
  15. *
  16. ERROR at line 1:
  17. ORA-06550: line 7, column 12:
  18. PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
  19. ORA-06550: line 7, column 1:
  20. PL/SQL: Statement ignored
  21.  
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.
Oct 5 '07 #8
amitpatel66
2,367 Expert 2GB
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??
Oct 5 '07 #9
amitpatel66
2,367 Expert 2GB
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.
Oct 5 '07 #10
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]
Oct 5 '07 #11
amitpatel66
2,367 Expert 2GB
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!!
Oct 5 '07 #12
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.
Oct 5 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by David Stephenson | last post: by
10 posts views Thread by N | last post: by
2 posts views Thread by Amin Schoeib | last post: by
7 posts views Thread by ranjeet.gupta | last post: by
24 posts views Thread by raashid bhatt | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.