Re: Subquery not valid in a cursor?

Hi
Thanks for your reply.
It does not appear to work for me:
Am I doing anything wrong?

                Open    Open_Orders_Cur
                For     Select  ORDER_NUMBER, SALES_ORDER_TYPE,
                                CUSTOMER_REFERENCE, ORDER_DATE, USERNAME,
                                WEIGHT, VOLUME, SOP_ORDER_TOTAL_CURRENCY,
                                STATUS_FLAG,
                                (Select Count(*)
                                Where   L.COMPANY_CODE = H.COMPANY_CODE
                                  And   L.DIVISION = H.DIVISION
                                  And   L.ORDER_NUMBER = H.ORDER_NUMBER;
                                        From    FINANCE.SALES_ORDER_HEADERS
H)
                        Where   H.COMPANY_CODE = P_Company
                          And   H.DIVISION = P_Division
                          And   H.CUSTOMER_NUMBER = P_Customer_Id
                          And   H.STATUS_FLAG <> 'C';

LINE/COL ERROR

-------- -----------------------------------------------------------------
108/6    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>

109/5    PLS-00103: Encountered the symbol "WHERE" when expecting one of
         the following:
         , * & - + / at mod rem <an identifier>
         <a double-quoted delimited-identifier> <an exponent (**)> as
         from ||

112/6 PLS-00103: Encountered the symbol "FROM"

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> a ecrit dans le message de news: a8h8pg$jid$1_at_babylon.agtel.net...
> Seems that this syntax is not supported in PL/SQL (PL/SQL is always at
least one
> step behind the SQL engine when it comes to SQL syntax). Similar syntax
works
> perfectly well in plain SQL in 8.1.7:
>
> SQL> select 1, 2, 3, (select sysdate from dual) d from dual
> /
>
> 1 2 3 D
> ---------- ---------- ---------- ---------
> 1 2 3 04-APR-02
>
>
> Using REF CURSOR you can workaround this problem (since SQL engine
> will be processing the statement in this case):
>
> ...
> TYPE myCursor IS REF CURSOR;
> Open_Order_Cur myCursor;
> ...
> BEGIN
> -- here's the main magic - PL/SQL engine will call SQL engine to parse
the
> -- statement and create the cursor, and it will work!
> OPEN Open_Order_Cur FOR '<your select here>';
> LOOP
> FETCH Open_Order_Cur INTO <wherever>;
> EXIT WHEN Open_Order_Cur%NOTFOUND;
> ....
> END LOOP;
> END;
>
> Here's a demo with my simple example query:
>
> create or replace procedure subqtest
> as
> type cur is ref cursor;
> c1 cur;
> a number;
> b number;
> c number;
> d date;
> begin
> dbms_output.enable(10000);
> open c1 for 'select 1 a, 2 b, 3 c, (select sysdate from dual) d from
dual';
> loop
> fetch c1 into a, b, c, d;
> exit when c1%notfound;
> dbms_output.put_line('a = '||a||' b = '||b||' c = '||c||' d = '||d);
> end loop;
> close c1;
> end;
> /
>
> Procedure created.
>
> SQL> set serveroutput on
> SQL> exec subqtest
> a = 1 b = 2 c = 3 d = 04-APR-02
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> --
> Vladimir Zakharychev (bob_at_dpsp-yes.com)
http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
applications.
> All opinions are mine and do not necessarily go in line with those of my
employer.
>
>
> "sylvain tremblay" <syltrem_at_videotron.ca> wrote in message
> news:8hQq8.43033$Be7.2088119_at_weber.videotron.net...
> > I forgot:
> > This is Oracle 816
> >
> > "sylvain tremblay" <syltrem_at_videotron.ca> a écrit dans le message de
news:
> > t0Qq8.42841$Be7.2073405_at_weber.videotron.net...
> > > Hi
> > >
> > > From the doc, subqueries are valid inside the FROM and WHERE clause
> > > but can someone tell me if this is valid? I get an error at compile
time.
> > > If it`s not valid, then how should I do this kind of query in PL/SQL ?
> > >
> > > Thanks
> > > Syltrem
> > >
> > > Cursor Open_Order_Cur
> > > is Select ORDER_NUMBER, SALES_ORDER_TYPE,
> > > CUSTOMER_REFERENCE, ORDER_DATE, USERNAME,
> > > WEIGHT, VOLUME, SOP_ORDER_TOTAL_CURRENCY,
> > > STATUS_FLAG,
> > > (Select Count(*)
> > > From FINANCE.SALES_ORDER_LINES L
> > > Where L.COMPANY_CODE = H.COMPANY_CODE
> > > And L.DIVISION = H.DIVISION
> > > And L.ORDER_NUMBER = H.ORDER_NUMBER)
> > > From FINANCE.SALES_ORDER_HEADERS H
> > > Where H.COMPANY_CODE = P_Company
> > > And H.DIVISION = P_Division
> > > And H.CUSTOMER_NUMBER = P_Customer_Id
> > > And H.STATUS_FLAG <> 'C';
> > >
> > >
> > >
> >
> >
>
Received on Thu Apr 04 2002 - 05:41:21 CST