Pass Thru Subselect Syntax Help 1

[nguenthe]

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')

 

[PHV]

What about this ?

SELECT A.*, (SELECT onhandqty FROM DOMDATA.PUB.icwhseItem WHERE itemnum=A.itemnum AND whse='RTL') AS yourAlias
FROM DOMDATA.PUB.icWhseItem A
WHERE A.CoNum = '001' AND A.WHSE = 'WH1' AND A.NonStock = 0 AND A.itemnum IN ('C5315X20X24','C5315X24')

Another way is a self join:

SELECT A.*, R.onhandqty
FROM DOMDATA.PUB.icWhseItem A INNER JOIN DOMDATA.PUB.icWhseItem R ON A.itemnum = R.itemnum 
WHERE A.CoNum = '001' AND A.WHSE = 'WH1' AND A.NonStock = 0 AND A.itemnum IN ('C5315X20X24','C5315X24') AND R.WHSE = 'RTL'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nguenthe]

The first method still bombed with the same errors I was initially getting. The second method worked perfectly! Thank you so much - this will make a HUGE performance difference.

 

[nguenthe]

I spoke too soon. The second method actually kind of worked, but only in the event where there is a matching record for the 'RTL' Whse . How can I get this to work even if the 'RTL' Whse does not have a record?

 

[PHV]

still bombed with the same errors
FYI we still don't know which errors ...

 

[PHV]

SELECT A.*, R.onhandqty
FROM DOMDATA.PUB.icWhseItem A LEFT JOIN (
SELECT itemnum,onhandqty FROM DOMDATA.PUB.icWhseItem WHERE WHSE = 'RTL'
) R ON A.itemnum = R.itemnum 
WHERE A.CoNum = '001' AND A.WHSE = 'WH1' AND A.NonStock = 0 AND A.itemnum IN ('C5315X20X24','C5315X24')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nguenthe]

Thank you - the left join worked and all is well.

As it relates to the first method, the error was:
ODBC--call failed. Syntax error in SQL statement at or about "SELECT onhandqty fromdomdata.pub.icwhse" (10713) (#-210056)

 

[PHV]

SELECT onhandqty from[highlight] [/highlight]domdata.pub.icwhse