Skip to Content
avatar image
Former Member

SQL Query onorder exclude Production order

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 19 at 11:51 AM

    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

    Add comment
    10|10000 characters needed characters exceeded