hi all., i want to know the diffrence between the decode and case function that is available in oracle....help me ya...
-
-
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. -
CASE can handle range operations also.Comment
-
decode is used for strings & case for numericsOriginally posted by SaiiComment
-
CASE can be used in SELECT statement as well as PLSQL Block
DECODE can be used only in SELECT statement.Comment
-
I found we cannot use CASE in PL/SQL.
We can use DECODE in PL/SQL.Comment
-
CASE in PLSQL: Works FineOriginally posted by laconicamit
[code=oracle]
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;
[/code]
CASE in SQL: Works fine
[code=oracle]
SELECT CASE WHEN empno = 1 THEN 'MANAGER' ELSE 'EMPLOYEE' END from emp
[/code]
DECODE in PLSQL: Errors out
[code=oracle]
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','EM PLOYEE');
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
[/code]
DECODE in SQL: Works fine
[code=oracle]
SELECT DECODE(empno,1, 'MANAGER','EMPL OYEE') from emp
[/code]
Clear??Comment
-
Originally posted by amitpatel66
DECODE in PLSQL: "Works Fine"
set serveroutput on;
DECLARE
emp_comm NUMBER;
BEGIN
SELECT DECODE(comm,NUL L,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.Comment
-
The solutions that I provided is from Oracle 9i version and not oracle 8i.Originally posted by laconicamit
come on wake up and check out the latest versions that oracle has released 9i,10g, 11g and still you talking about 8i??Comment
-
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.Originally posted by laconicamitComment
-
What happened to you Dear!! Why you are acting as Loser!!!Originally posted by amitpatel66
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]Comment
-
Both of your above statements are wrong!!Originally posted by laconicamit
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!!Comment
-
Originally posted by amitpatel66
Thank you!!
Many thanks for your advice.Comment
Comment