we are trying to pull all locations from the PA1 that have containers, so this query seems to work, problem is that there are locations that DON't have a container, so we need to pull those also. Why will the below not work. PLease help we need this asap.
select i.whse,i.loc,ct.container_num, i.item,it.description, case when c.qty_contained is null then qty_on_hand else c.qty_contained end as qty
from itemloc_mst i
inner join item_mst it on it.item = i.item
left outer join container_item_mst c on c.item = i.item
left outer join container_Mst ct on ct.container_num = c.container_num and ct.whse = i.whse
where i.qty_on_hand > 0
and i.whse ='pa1' and ct.whse = 'pa1'
and i.loc like '20I01%'
and c.item is null
order by i.loc desc
An INNER join on container_item_mst and/or container_mst should give you what you want, too. And it may become necessary if your application stores items in a way that the new data will have characteristics to what you just saw.
Hi Steven,
An INNER join on container_item_mst and/or container_mst should give you what you want, too. And it may become necessary if your application stores items in a way that the new data will have characteristics to what you just saw.
Good Luck,
Kent