| How to return only one row when no easy link? [message #372531] |
Tue, 20 February 2001 12:41  |
Kieron Smythe
Messages: 11
Registered: March 2000
|
Junior Member |
|
|
Hello all Oracle boffins,
I have a query:
SELECT sha.header_id,
sha.order_number,
sla.line_id,
sld.line_detail_id,
sla.line_number,
sha.customer_id,
we.wip_entity_name,
sld.quantity,
Sld.schedule_date
FROM So_headers sha,
Mtl_system_items msi,
Wip_so_allocations wsa,
Wip_entities we,
So_line_details sld,
wip_discrete_jobs wdj,
So_lines sla
WHERE Sla.s27 = 21
AND sla.line_id = wsa.demand_source_line
AND wsa.wip_entity_id = we.wip_entity_id
AND sla.inventory_item_id = msi.inventory_item_id
AND sla.warehouse_id = msi.organization_id
AND sla.header_id = sha. header_id
AND sla.line_id = sld.line_id
AND sld.quantity = wsa.quantity_allocated
AND wdj.wip_entity_id = we.wip_entity_id
/
It returns:
HEADER_ID ORDER_NUMBER LINE_ID LINE_DETAIL_ID LINE_NUMBER CUSTOMER_ID QUANTITY SCHEDULE_
--------- ------------ --------- -------------- ----------- ----------- --------- ---------
1441 213 1542 1359 2 4308 6 02-FEB-01
1441 213 1540 1356 1 4308 5 02-FEB-01
1441 213 1540 1358 1 4308 5 02-FEB-01
1441 213 1540 1356 1 4308 5 02-FEB-01
1441 213 1540 1358 1 4308 5 02-FEB-01
Now, I know why it returns duplicates. There is no link between the detail_lines table and the wip_entities table.
How do I restrict it to only return:
HEADER_ID ORDER_NUMBER LINE_ID LINE_DETAIL_ID LINE_NUMBER CUSTOMER_ID QUANTITY SCHEDULE_
--------- ------------ --------- -------------- ----------- ----------- --------- ---------
1441 213 1542 1359 2 4308 6 02-FEB-01
1441 213 1540 1356 1 4308 5 02-FEB-01
1441 213 1540 1358 1 4308 5 02-FEB-01
All help appreciated!
Regards,
Kieron
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
