SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LEFT Join Subquery Problem

    I am tired of banging my head on the wall with this. I could really use some help.

    I have a table "network_order" (simplified):
    order_number
    parent_order_number
    version_number
    product_type
    order_status
    order_service_type
    requested_due_datetime

    This table might contain multiple "parent" orders and also contains it's "child" orders. The "parent" order would have an "order_number" and nothing (NULL) in the "parent_order_number" field. The "child" orders would each have their own "order_number" but their parent order would be in the "parent_order_number" field. Each of these orders might also have a version_number. So, the same order would be in the table multiple times with different version numbers.

    I need to do the following:
    Identify each parent order (the highest version) and the count of all child orders (the highest version) associated with each parent order. It should also list all orders that don't have children and return NULL for the count.

    I'd REALLY like to do this in a single query. I know it can be done. Here is what I am doing:

    Code:
    	select neto.order_number, neto.REQUESTED_DUE_DATETIME as due_date, pcount.parent_count
    	from network_order neto,
    		(select max(network_order.version_number) as max_version,order_number
    			from network_order
    			group by order_number) mv
    	left join (select count(distinct order_number) as parent_count,parent_order_number from network_order group by parent_order_number) as pcount
    		on network_order.order_number  = pcount.order_number
    	where neto.assigned_group = 'NG_AIN' AND
    	neto.order_service_type = 'ISUP' AND
    	neto.product_type = 'ISUP' AND
    	neto.order_number = mv.order_number AND
    	neto.version_number = mv.max_version AND
    	neto.order_status = '0' AND
    	neto.order_number = pcount.parent_order_number
    	order by order_number

    Unfortunately, I can't get past this error:
    ORA-00905: missing keyword : Error at line 6, column 132

    Oh yes : Oracle database.

    Any clues what I'm doing wrong?
    Last edited by justbn_me; Apr 4, 2008 at 14:09.
    Justin Noel
    Better Than Broadway - The Best Local Theatre Calendar
    www.betterthanbroadway.com/

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the problem is here:
    Code:
    left join (
    select count(distinct order_number) as parent_count
         , parent_order_number 
      from network_order 
    group by parent_order_number) as pcount
    on network_order.order_number  = pcount.order_number
    the pcount derived table (subquery) does not have a column called order_number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •