Results 1 to 2 of 2
Thread: LEFT Join Subquery Problem
Apr 4, 2008, 11:45 #1
- 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 13:09.Justin Noel
Better Than Broadway - The Best Local Theatre Calendar
Apr 4, 2008, 21:46 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 63 Post(s)
- 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