LEFT Join Subquery Problem

[justbn_me]

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:

	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?

[r937]

the problem is here:

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