Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

using data from different warehouse in a query

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 Member
Former Member replied

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

...

..

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question