Skip to Content
avatar image
Former Member

Query Item Master Data and Sales Quotation

Dear Experts,

I want to make a query that join the information on Item Master Data (including UDF) and Sales Quotation Quantity.

I found error when I want to insert the time range for sales quotation in this query. I believe the error is at AND T10.DocDate >= '[%0]' AND T10.DocDate <= '[%1] below

Please help to solve this simple query... Thank you for your help

select A.ItemCode
,A.ItemName
,A.ItmsGrpCod
,C.ItmsGrpNam
,B1.WhsCode
,B.WhsName


,A.InvntryUom [Inv. Uom]
,B1.OnHand [OnHand]
,B1.OnOrder [Open PO]
,B1.IsCommited [Open SO]
,(select SUM(Quantity) FROM QUT1 T10 WHERE T10.ItemCode = A.ItemCode AND T10.DocDate >= '[%0]' AND T10.DocDate <= '[%1]) [SQ Qty]
,(B1.OnHand+B1.OnOrder)-B1.IsCommited [Available]
,D.DocDate [Transfer Date]
,DATEDIFF(DAY, D.DocDate, GETDATE()) [Aging Days]
,A.U_MIS_AgeSubcat, A.U_MIS_BserProd, A.U_MIS_BTypeCat,
A.U_MIS_ConCat, A.U_MIS_CurLev, A.U_MIS_PubBrand,
A.U_MIS_Vendor, A.U_MIS_PubYear, A.U_FS_INS


from OITM A 
LEFT JOIN OITB C ON A.ItmsGrpCod = C.ItmsGrpCod
LEFT JOIN OITW B1 ON A.ItemCode = B1.ItemCode
LEFT JOIN OWHS B ON B1.WhsCode = B.WhsCode
LEFT JOIN
(select MAX(T0.DocDate) [DocDate],
T0.ItemCode, T0.Warehouse
from OINM T0 where TransType = 67 AND InQty>0
group by T0.ItemCode, T0.Warehouse) D
ON A.ItemCode = D.ItemCode AND B1.WhsCode = D.Warehouse
WHERE B1.WhsCode LIKE '%' + '[%2]' + '%' AND A.U_FS_INS='yes'
ORDER BY A.ItemCode, B1.WhsCode
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 26, 2017 at 06:24 AM

    Hi,

    You are right, the bit you mentioned is missing a quote:

    select A.ItemCode
    ,A.ItemName
    ,A.ItmsGrpCod
    ,C.ItmsGrpNam
    ,B1.WhsCode
    ,B.WhsName
    ,A.InvntryUom [Inv. Uom]
    ,B1.OnHand [OnHand]
    ,B1.OnOrder [Open PO]
    ,B1.IsCommited [Open SO]
    ,(select SUM(Quantity) FROM QUT1 T10 WHERE T10.ItemCode = A.ItemCode AND T10.DocDate >= '[%0]' AND T10.DocDate <= '[%1]') [SQ Qty]
    ,(B1.OnHand+B1.OnOrder)-B1.IsCommited [Available]
    ,D.DocDate [Transfer Date]
    ,DATEDIFF(DAY, D.DocDate, GETDATE()) [Aging Days]
    ,A.U_MIS_AgeSubcat, A.U_MIS_BserProd, A.U_MIS_BTypeCat,
    A.U_MIS_ConCat, A.U_MIS_CurLev, A.U_MIS_PubBrand,
    A.U_MIS_Vendor, A.U_MIS_PubYear, A.U_FS_INS
    from OITM A 
    LEFT JOIN OITB C ON A.ItmsGrpCod = C.ItmsGrpCod
    LEFT JOIN OITW B1 ON A.ItemCode = B1.ItemCode
    LEFT JOIN OWHS B ON B1.WhsCode = B.WhsCode
    LEFT JOIN
    (select MAX(T0.DocDate) [DocDate],
    T0.ItemCode, T0.Warehouse
    from OINM T0 where TransType = 67 AND InQty>0
    group by T0.ItemCode, T0.Warehouse) D
    ON A.ItemCode = D.ItemCode AND B1.WhsCode = D.Warehouse
    WHERE B1.WhsCode LIKE '%' + '[%2]' + '%' AND A.U_FS_INS='yes'
    ORDER BY A.ItemCode, B1.WhsCode

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 26, 2017 at 05:41 AM
    Try this...
    
    
    select A.ItemCode
    ,A.ItemName
    ,A.ItmsGrpCod
    ,C.ItmsGrpNam
    ,B1.WhsCode
    ,B.WhsName
    ,A.InvntryUom [Inv. Uom],B1.OnHand [OnHand],B1.OnOrder [Open PO],B1.IsCommited [Open SO],(selectSUM(Quantity)FROM QUT1 T10 WHERE T10.ItemCode = A.ItemCode AND T10.DocDate >='[%0]'AND T10.DocDate <= '[%1]')[SQ Qty],(B1.OnHand+B1.OnOrder)-B1.IsCommited [Available],D.DocDate [TransferDate],DATEDIFF(DAY, D.DocDate, GETDATE())[Aging Days],A.U_MIS_AgeSubcat, A.U_MIS_BserProd, A.U_MIS_BTypeCat,
    A.U_MIS_ConCat, A.U_MIS_CurLev, A.U_MIS_PubBrand,
    A.U_MIS_Vendor, A.U_MIS_PubYear, A.U_FS_INS
    from OITM A 
    LEFTJOIN OITB CON A.ItmsGrpCod =C.ItmsGrpCod
    LEFTJOIN OITW B1 ON A.ItemCode = B1.ItemCode
    LEFTJOIN OWHS B ON B1.WhsCode = B.WhsCode
    LEFTJOIN(selectMAX(T0.DocDate)[DocDate],
    T0.ItemCode, T0.Warehouse
    from OINM T0 where TransType =67AND InQty>0groupby T0.ItemCode, T0.Warehouse) D
    ON A.ItemCode = D.ItemCode AND B1.WhsCode = D.Warehouse
    WHERE B1.WhsCode LIKE'%'+'[%2]'+'%'AND A.U_FS_INS='yes'ORDERBY A.ItemCode, B1.WhsCode
    Add comment
    10|10000 characters needed characters exceeded