I am trying to improve some queries by doing the subselects against the same table (but referencing other records). Something is incorrect in my syntax and I have tried a bunch of variations of the referenced table and nothing seems to work. Can anyone please help?
The goal is to get every field in icWhseItem and then one additional field named 'RTL' for the onhandqty.
SELECT icWhseItem_A.*, (SELECT icWhseItem.onhandqty
FROM PUB.icwhseItem
WHERE icWhseItem.itemnum = itemnum and icWhseItem.whse = 'RTL')
FROM DOMDATA.PUB.icWhseItem icWhseItem_A
WHERE icWhseItem_A.CoNum = '001' AND icWhseItem_A.WHSE = 'WH1' AND icWhseItem_A.NonStock = 0 AND icwhseItem_A.itemnum in ('C5315X20X24','C5315X24')
The goal is to get every field in icWhseItem and then one additional field named 'RTL' for the onhandqty.
SELECT icWhseItem_A.*, (SELECT icWhseItem.onhandqty
FROM PUB.icwhseItem
WHERE icWhseItem.itemnum = itemnum and icWhseItem.whse = 'RTL')
FROM DOMDATA.PUB.icWhseItem icWhseItem_A
WHERE icWhseItem_A.CoNum = '001' AND icWhseItem_A.WHSE = 'WH1' AND icWhseItem_A.NonStock = 0 AND icwhseItem_A.itemnum in ('C5315X20X24','C5315X24')