Re: Works in SQL*Plus but not in PL/SQL

From: Richard Kuhler <noone_at_nowhere.com>
Date: Wed, 05 Jun 2002 22:16:42 GMT
Message-ID: <e5wL8.48603$R53.17239105@twister.socal.rr.com>

There are many places that subqueries aren't allowed in PL/SQL prior to 9i. In SQL Functions or as a select clause come to mind. In your case, you should be able to change that query to ...

update rh_1 rp
set gid =

   (select nvl(min(rp2.gid), rp.gid)
   from rh_1 rp1, rh_1 rp2
   where rp.gid = rp1.gid

     and rp1.dl = rp2.dl
     );

Richard

Bill Hoernke wrote:
>
> I have searched the group and found some similar things but I haven't
> solved my problem yet. Any help would be appreciated.
>
> This update statement works in SQL*Plus, but it doesn't work when I
> try to compile it in a PL/SQL Package.
>
> update rh_1 rp
> set gid =
> nvl((select min(rp2.gid)
> from rh_1 rp1, rh_1 rp2
> where rp.gid = rp1.gid
> and rp1.dl = rp2.dl
> ),gid);
>
> I get PLS-00103: Encountered the symbol "SELECT" when expecting one of
> the following: ( - + ....
>
> Thanks,
> Bill Hoernke
> GartonWorks, Inc.
Received on Wed Jun 05 2002 - 17:16:42 CDT