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 <an identifier>
<a double-quoted delimited-identifier> <a bind variable> 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).
