RE: Case stmt not compiling
or wrap it within execute immediate hth connor --- Ganesh Raja <[EMAIL PROTECTED]> wrote: > This is due to the difference betweeen the PL/SQL > Compiler and SQL > Compiler. This has been rectified from 9i onwards > > One way to achive this is to Create a view and > access this View in > Pl/sql. > > HTH > > Best Regards, > Ganesh R > Tel : +971 (4) 397 3337 Ext 420 > Fax : +971 (4) 397 6262 > HP : +971 (50) 745 6019 > > Live to learn... forget... and learn again. > > > > -Original Message- > [EMAIL PROTECTED] > Sent: Friday, June 14, 2002 10:03 AM > To: Multiple recipients of list ORACLE-L > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Ganesh Raja > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Some days you're the pigeon, some days you're the statue" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Case stmt not compiling
Another alternative, no better or worse than Ganesh's reply, is to use
native dynamic SQL:
Declare
l_ln_status varchar2(1);
Begin
execute immediate 'select (case when ''N'' in (''C'',''N'',''U'',''V'') then
''C''
else (case when ''N'' = ''P'' then ''P''
end)
end) from dual' into l_ln_status;
dbms_output.put_line('Here: '||l_ln_status);
end;
/
Now I *really* have to ask, though, why the original poster, Manoj, wants to
do the CASE statement selecting from dual when a simple IF/THEN/ELSE
construct could be used to populate the variable. Why bring a SQL statement
and dual into the mix? Or was this just a sample query to illustrate the
question?
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
> -----Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ganesh Raja
> Sent: Friday, June 14, 2002 2:23 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Case stmt not compiling
>
>
> This is due to the difference betweeen the PL/SQL Compiler and SQL
> Compiler. This has been rectified from 9i onwards
>
> One way to achive this is to Create a view and access this View in
> Pl/sql.
>
> HTH
>
> Best Regards,
> Ganesh R
> Tel : +971 (4) 397 3337 Ext 420
> Fax : +971 (4) 397 6262
> HP : +971 (50) 745 6019
>
> Live to learn... forget... and learn again.
>
>
>
> -Original Message-
> [EMAIL PROTECTED]
> Sent: Friday, June 14, 2002 10:03 AM
> To: Multiple recipients of list ORACLE-L
Hi,
The following case stmt is not compiling in stored proc.
select (case when 'N' in ('C','N','U','V') then 'C' else (case when
'N' = 'P' then 'P'
end) end)
into l_ln_status
from dual;
I get the following error :
PLS-00103: Encountered the symbol "CASE" when expecting one of the
following:
( - + mod not null others
avg
count cur
the same sql runs at sqlprompt.
Thanks
Manoj.
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Case stmt not compiling
This is due to the difference betweeen the PL/SQL Compiler and SQL Compiler. This has been rectified from 9i onwards One way to achive this is to Create a view and access this View in Pl/sql. HTH Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- [EMAIL PROTECTED] Sent: Friday, June 14, 2002 10:03 AM To: Multiple recipients of list ORACLE-L -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ganesh Raja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
