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
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?