Path: news.easynews.com!easynews!cyclone.swbell.net!cyclone-sf.pbi.net!216.218.192.242!news.he.net!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: markg@mymail.co.uk (Mark) Newsgroups: comp.databases.oracle.server Subject: Re: Subquery not valid in a cursor? Date: 4 Apr 2002 04:28:06 -0800 Organization: http://groups.google.com/ Lines: 31 Message-ID: References: NNTP-Posting-Host: 163.166.150.22 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1017923287 28105 127.0.0.1 (4 Apr 2002 12:28:07 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 4 Apr 2002 12:28:07 GMT Xref: easynews comp.databases.oracle.server:142012 X-Received-Date: Thu, 04 Apr 2002 05:25:54 MST (news.easynews.com) This syntax works in a particular verson of Oracle and SQL (can't remember version numbers). Try the Oracle website to find out if it works for the Oracle version and SQL /PLSQL version you are using. M "sylvain tremblay" wrote in message news:... > 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';