Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Decode equivalent within pl/sql?
In article <455888e9$0$30316$9b4e6d93_at_newsspool1.arcor-online.net>,
Maxim Demenko says...
> 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
>
Thanks I may not have made it clear - I know I can use DECODE in a select stamenet within PL/SQL but I was looking for a nicer way to code the following (example) without having to define a variable into which to put the value I want before calling the proc.
e.g
begin
Now I know I cannot use the decode there and it seems that I have to
look at the value of "thing" using either
"if then else"
or a
"select decode(....) into var from dual;"
-- jeremy ... .--. .-. .. -. - / ... -Received on Mon Nov 13 2006 - 09:13:00 CST