using data from different warehouse in a query

[Former Member]

Hi guys, I regularly use the query below to get stock information from SAP. However, i would like the 'on order' column to be from a different warehouse to the other data on the query.

SELECT T0.[ItemCode], T1.[ItemName], T0.[WhsCode], T0.[OnHand], T0.[IsCommited], T0.[OnOrder], ( T0.[OnHand]- T0.[IsCommited]) 'Available to Sell',

' ' ' ',

t0.avgprice, t2.price 'Factory Cost Price', t2.currency 'Curr' ,

(select rate from ortt where DATEDIFF(day, ratedate, GETDATE()) = 0 and currency = t2.currency) 'Todays Rate',

(t2.price / (select rate from ortt where DATEDIFF(day, ratedate, GETDATE()) = 0 and currency = t2.currency)) 'GBP @ Todays Rate'

FROM [dbo].[OITW] T0, OITM T1, itm1 t2

where T0.ItemCode = T1.ItemCode

and t2.itemcode = t1.itemcode

and t2.pricelist = 2

and T0.WhsCode in ('pinnacle') and (t1.usertext not like N'000%' or t1.usertext is null) order by T0.ItemCode

as you can see, the warehouse i am using is Pinnacle, but we use Transit for goods that are on order. Is it possible to isolate t0.OnOrder using brackets?

thanks

[former_member583013 · Accepted solution]

Groovy,

We need to have a sub query which will be something like

SELECT T0.ItemCode, T1.ItemName, T0.WhsCode, T0.OnHand, T0.IsCommited, T0.OnOrder, (SELECT OnOrder FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode = 'Tansit'),

( T0.OnHand- T0.IsCommited) 'Available to Sell',

...

..