Skip to Content
0

SQL Query onorder exclude Production order

Feb 19 at 10:51 AM

24

avatar image
Former Member

Hi, I would like to make a query that shows stock and order information about some articles. But I want to exclude the amount "onorder" and "iscommited that are related to PO, I only want to se OR. How do I do that?

SELECT T0.[ItemCode], T0.[ItemName], T1.[OnHand] - T1.[IsCommited], T1.[OnHand], T1.[IsCommited], T1.[OnOrder] FROM OITM T0 INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] WHERE T1.[WhsCode] = '01' AND T0.[ItmsGrpCod] = 103 AND T0.[ItemCode] LIKE 'O%'

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Johan Hakkesteegt Feb 19 at 11:51 AM
0

Hi David,

You can accomplish this by specifically including purchase and sales orders instead of excluding production orders:

SELECT i.ItemCode
      ,i.OnHand
      ,ISNULL((select sum(t.OpenQty) from por1 t where t.ItemCode = i.ItemCode), 0) AS OnOrder
      ,ISNULL((select sum(t.OpenQty) from rdr1 t where t.ItemCode = i.ItemCode), 0) AS IsCommited
FROM OITM i

Regards,

Johan

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Great, worked fine, Thanx

1