RE: Case stmt not compiling

From: Larry Elkins <elkinsl_at_flash.net>
Date: Fri, 14 Jun 2002 03:58:22 -0800
Message-ID: <F001.0047DE8A.20020614035822@fatcity.com>

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
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]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-----
> cosltemp-g.manoj_at_orbitech.co.in
> 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: elkinsl_at_flash.net

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: ListGuru_at_fatcity.com (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).

Received on Fri Jun 14 2002 - 06:58:22 CDT