Re: select from (select...)

Oren Nakdimon wrote:
>
> Hi.
> I'm trying to do a query with sub-query in the FROM clause, in PL/SQL
> (Oracle 7.3.3, PL/SQL 2.3.2.3.1 on Windows NT 4).
> The sub-query is using some variables, and it's not working.
>
> This is a simple example:
>
> declare
> a number;
> r dual%rowtype;
> begin
> select *
> into r
> from ( select *
> from dual
> where a=a );
> end;
> /
>
> When I don't use variables in the sub-query it works.
> It works also when I use the sub-query in the FROM clause of other
> statements (e.g. DELETE).
>
> Can someone help me ?
> Thanks, Oren.

Seems like your simple example may have been too simplified to illustrate what you're really trying to accomplish, otherwise I would think you could just do:

 declare

    a number := 1;
    r dual%rowtype;
 begin

    select *
    into r
    from dual
      where a=a;
 end;
 /

But if you really need the subquery you could do:

declare

   a number := 1;
   r dual%rowtype;
begin

   select *
   into r
   from dual where exists

        ( select *
          from dual
          where a = a );

Glen
UW-Extension Received on Tue Jun 24 1997 - 00:00:00 CDT