Query for item warehouses stock in a row

[former_member187989]

hi all,

How to retreive item warehouse stock in a row using query ?

SELECT T0.ItemCode, T1.ItemName, T2.ItmsGrpNam, 
(Select WhsCode from OITW Where
WhsCode=T0.WhsCode and ItemCode=T0.ItemCode) as 'Wh',
(Select OnHand From OITW Where
Whscode = T0.WhsCode and Itemcode=T0.ItemCode) as 'Whstck' 
FROM OITW T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod 
WHERE T0.ItemCode NOT IN 'a-item'AND
T2.ItmsGrpNam LIKE '%%test%%' 
and T0.Onhand > 0 
ORDER BY T0.ItemCode

Anyone correct query so that it will retrieve item warehouse stock in a single row.

Thanks

Jeyakanthan

[Former Member · Accepted solution]

I have tested your query, the result is just like you wish. The code can be simplified to:

SELECT T0.ItemCode, T1.ItemName, T2.ItmsGrpNam, T0.WhsCode as 'Wh',T0.OnHand as 'Whstck' 
FROM DBO.OITW T0  
INNER JOIN DBO.OITM T1 ON T0.ItemCode = T1.ItemCode 
INNER JOIN DBO.OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod 
WHERE T0.ItemCode NOT IN ('a-item') AND T2.ItmsGrpNam LIKE '%[%test%]%' 
and T0.Onhand > 0 
ORDER BY T0.ItemCode, T0.WhsCode

If you want each item has only one row and warehouse on hand column-wised, you have to use as much as SELECT for each column in according to your number of warehouses. It would be look like this one:

SELECT T0.ItemCode, T0.ItemName, T1.ItmsGrpNam, 
(SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like 'Whs1') AS 'Whs1 On Hand' , 
(SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like 'Whs2') AS 'Whs2 On Hand' , 
(SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like 'Whs3') AS 'Whs3 On Hand' 
FROM DBO.OITM T0  
INNER JOIN DBO.OITB T1 ON T1.ItmsGrpCod = T0.ItmsGrpCod 
WHERE T0.ItemCode NOT IN ('a-item') AND T1.ItmsGrpNam LIKE '%[%test%]%' 
and T0.Onhand > 0 
ORDER BY T0.ItemCode

Thanks,

Gordon

[marcolde]

hi,

would anyone how i can just show one line per item and the qty of each warehouse next to each other?

the above querie works for me however if one products is available in three warehouses i see three lines and if it is available in four warheouse i get four lines

[former_member187989]

Thanks gordon,Thank you all.

Jeyakanthan

[Former Member]

THIS WILL LIST ALL ITEMS AND WAREHOUSES

SELECT T0.WhsCode, T0.ItemCode, T1.ItemName, T0.OnHand, T0.AvgPrice, ( T0.OnHand * T0.AvgPrice) AS 'TOTAL' , T0.StockValue FROM OITW T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode WHERE T T0.OnHand >0 ORDER BY T0.WhsCode, T0.ItemCode

[former_member187989]

hi,

I am not applying query in FMS,i require this as a report.

Jeyakanthan

[Former Member]

HI Jeyakanthan,

do you want to sum the hole quantity of all warehouses ? Otherwise i do not really understand what do you want to see. Your query runs well without 'a-item'

Regards Steffen

[Former Member]

Hi Jeyakanthan,

i used the following query as FMS;

SELECT T0.[OnHand] FROM OITW T0 WHERE T0.[ItemCode] =$[inv1.itemcode] and T0.[WhsCode]=$[inv1.whscode]

this query will give you the instock of the default warehouse for that item. Hope this will help you.

Regards,

Joseph