Home » SQL & PL/SQL » SQL & PL/SQL » Case and Decode
Case and Decode [message #243892] Sun, 10 June 2007 18:48 Go to next message
spooja
Messages: 25
Registered: May 2007
Junior Member
Can we use Case, Decode and NVL Statements in PL/SQL Code(eg in Stored Procedure, Function etc.)
Re: Case and Decode [message #243895 is a reply to message #243892] Sun, 10 June 2007 22:00 Go to previous messageGo to next message
san_bajpai75
Messages: 4
Registered: June 2007
Junior Member
you can use case and decode in sql/plsql statement.
prefer case statement .
Re: Case and Decode [message #243943 is a reply to message #243892] Mon, 11 June 2007 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just testing it?
SQL> declare
  2     i integer;
  3  begin
  4     i := case when i is null then 1 end;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> declare
  2     i integer;
  3  begin
  4     i := decode(i,1,2,3);
  5  end;
  6  /
   i := decode(i,1,2,3);
        *
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored


SQL> declare
  2     i integer;
  3  begin
  4    i := nvl(i,1);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Regards
Michel
Re: Case and Decode [message #243944 is a reply to message #243895] Mon, 11 June 2007 01:39 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That is NOT true. DECODE cannot be used in PL/SQL:
SQL> SELECT banner
  2  FROM   v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> DECLARE
  2    myvar PLS_INTEGER := 3;
  3    myothervar PLS_INTEGER;
  4  BEGIN
  5    myothervar := DECODE(myvar,2,3,4);
  6  END;
  7  /
  myothervar := DECODE(myvar,2,3,4);
                *
ERROR at line 5:
ORA-06550: line 5, column 17:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL
statement only
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
Functions that can be used in PL/SQL are listed in the PL/SQL user's guide and reference. For example, you will see that NVL is listed there, but NVL2 isn't. Let's check:

SQL> DECLARE
  2    myvar      PLS_INTEGER := 3;
  3    myothervar PLS_INTEGER;
  4  BEGIN
  5    myothervar := NVL(myvar,2);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    myvar      PLS_INTEGER := 3;
  3    myothervar PLS_INTEGER;
  4  BEGIN
  5    myothervar := NVL2(myvar,-1,-2);
  6  END;
  7  /
  myothervar := NVL2(myvar,-1,-2);
                *
ERROR at line 5:
ORA-06550: line 5, column 17:
PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored


SQL>
SQL> SELECT NVL2(NULL,1,2) x
  2  FROM   dual
  3  /

         X
----------
         2


You're quick, Michel!
MHE

[Updated on: Mon, 11 June 2007 01:40]

Report message to a moderator

Re: Case and Decode [message #243952 is a reply to message #243944] Mon, 11 June 2007 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't know for NVL2! Very weird as COALESCE works.
Message should be the same as for DECODE and not this "identifier 'NVL2' must be declared".
A developer misses a function in his list. Wink

Btw, I always wonder why DECODE was never implemented in PL/SQL.

Regards
Michel
Re: Case and Decode [message #243956 is a reply to message #243952] Mon, 11 June 2007 02:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Mon, 11 June 2007 08:57
I didn't know for NVL2! Very weird as COALESCE works.
COALESCE is in the list. NVL2 isn't.
Michel Cadot wrote on Mon, 11 June 2007 08:57
Message should be the same as for DECODE and not this "identifier 'NVL2' must be declared"
Who am I to tell Oracle what error message they should return. But I recall this thread from asktom.oracle.com where he talks about NVL2. As for DECODE in PL/SQL, I'm comfortable using a CASE construction. But I never understood it either.

MHE
Re: Case and Decode [message #243968 is a reply to message #243956] Mon, 11 June 2007 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As Tom said:
Quote:
nvl2 is a function, not sql, they apparently forgot to "map the function"

Regards
Michel
Re: Case and Decode [message #243969 is a reply to message #243968] Mon, 11 June 2007 02:22 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And it still is consistent with my findings. Not in the document, not applicable in PL/SQL Razz

I knew you would come up with that, but I've posted the same remark in that thread Wink. In the end it doesn't matter. You cannot use it in PL/SQL. Most people don't know NVL2 anyway.

MHE
Previous Topic: Logon trigger
Next Topic: how can we get multiple SUM() from single table
Goto Forum:
  


Current Time: Sat Dec 10 20:45:18 CST 2016

Total time taken to generate the page: 0.09677 seconds