Re: Question on Hierarchy

From: Ronnie <ronnie_yours_at_yahoo.com>
Date: 20 Aug 2003 07:03:39 -0700
Message-ID: <ea603f8d.0308200603.5abd5c84@posting.google.com>

Hi,

Thanks a lot. The solution works when I run the query from sqlplus, but when i try to use the same query inside plsql it fails.

This is what I tried

SQL> declare
  2 answer varchar2(1);
  3 begin
  4
  5 select

  6      nvl((select 'Y' from dual where exists
  7          (select 1 from father_sons
  8           where son = 'd'
  9           connect by prior son = father
 10           start with father = 'a')),'N') into answer
 11      from dual;

 12 end;
 13 /

    nvl((select 'Y' from dual where exists

         *
ERROR at line 6:

ORA-06550: line 6, column 10:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the
following:

( - + mod not null others <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev sum variance execute forall time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string>

ORA-06550: line 10, column 35:
PLS-00103: Encountered the symbol "," when expecting one of the
following:

; return returning and or

Please suggest.

Thanks
Ron

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:<3f435042$0$10357$afc38c87_at_news.optusnet.com.au>...
> Oh yes: I'll bet you never saw a sub-query
> used inside a NVL function, eh?
> It works and very well.
>
> --
> Cheers
> Nuno Souto
> wizofoz2k_at_yahoo.com.au.nospam
> "Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:73e20c6c.0308192242.2fe3f007_at_posting.google.com...
> > SQL> select
> > 2 nvl((select 'Y' from dual where exists
> > 3 (select 1 from zot
> > 4 where f2 = 'd'
> > 5 connect by prior f2 = f1
> > 6 start with f1 = 'a')),'N')
> > 7 from dual;
Received on Wed Aug 20 2003 - 09:03:39 CDT