Jeff
asked on
iSeries DB2 SQL - How to select all records for an item where only 1 record meets "Where" criteria?
I need to query my inventory and select all records for all items where 1 of the records for the item has a last received date older than 20160201. My inventory contains over 11,000 items.
For example:
Item 123456 is in inventory on 5 pallets
Pallet 00001 was received 20160105
Pallet 00002 was received 20160605
Pallet 00003 was received 20160605
Pallet 00004 was received 20160705
Pallet 00005 was received 20160705
Currently, my code just grabs Pallet 00001 because the last received rate meets the criteria.
For example:
Item 123456 is in inventory on 5 pallets
Pallet 00001 was received 20160105
Pallet 00002 was received 20160605
Pallet 00003 was received 20160605
Pallet 00004 was received 20160705
Pallet 00005 was received 20160705
Currently, my code just grabs Pallet 00001 because the last received rate meets the criteria.
Select tblinv.pallet, tblinv.item, tblitm.description, tblinv.qty, tblinv.aisle, tblinv.slot, tblitm.lastrec
from tblinv
left join tblitm on tblinv.item = tblitm.item
where tblitm.lastrec < '20160201'
order by tblinv.item, tblitm.lastrec
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER