Skip to Content
0

Query Item Master Data and Sales Quotation

Apr 26, 2017 at 04:49 AM

95

avatar image
Former Member

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
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Johan Hakkesteegt Apr 26, 2017 at 06:24 AM
0

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

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

Thank you very much for your help!

0
Kennedy T Apr 26, 2017 at 05:41 AM
0
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
Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank you for your help, but I still get error.....

0