Re: Decode equivalent within pl/sql?

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Mon, 13 Nov 2006 16:00:12 +0100
Message-ID: <455888e9$0$30316$9b4e6d93@newsspool1.arcor-online.net>

Jeremy schrieb:
> 9iR2
>
> As I understand it you can only use DECODE as part of a SQL statement
>
> e.g. select decode(var,
> 'A','LetterA',
> 'B','LetterB',
> 'Lettersomethingelse')
> from dual;
>
> In a pl/sql statement, you cannot code e.g.
>
> l_string := decode(.....
>
> What do you use to achieve a similar result - or do you set the value
> into a variable beforehand with an
> if
> elsif
> end if
>
> structure?
>
> Thanks
>

Decode was introduced to make IF ELSE logic available in SQL, later came CASE which can do it as well, in PL SQL the IF ELSE logic was always available. In your example i would probably use IF ELSE ( while no data from tables are involved in your logic). But, to round up - in 9iR2 the appropriate use of decode in plsql is possible.

scott_at_CMS42DEV> SELECT * FROM V$VERSION;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.6.0 - Production NLSRTL Version 9.2.0.6.0 - Production

scott_at_CMS42DEV> DECLARE

   2 l_one NUMBER;
   3 l_two NUMBER;
   4 BEGIN
   5 SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END INTO l_one FROM dual;    6 dbms_output.put_line(l_one);
   7 SELECT decode(2,2,2) INTO l_two FROM dual;    8 dbms_output.put_line(l_two);
   9 END;
  10 /
1
2

Best regards

Maxim Received on Mon Nov 13 2006 - 09:00:12 CST