SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Threaded View

  1. #1
    SitePoint Member
    Join Date
    Apr 2006
    0 Post(s)
    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):

    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:

    	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


Posting Permissions

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