07-16-2020 7:13 PM
Hello,
Can any one help with the query bellow. My SQL is not very strong and I'm wanting to to have a user input variable where '51-6300' currently sits in the below statement, however when I try to use the standard [%0] SAP B1 input variable I get an error "Must specify table to select from"
SELECT
T0.DocEntry, T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty], T1.[TreeType], T1.[ShipDate]'ESD', T0.[DocDate]'Date Ordered',
CASE WHEN T1.TreeType='I' THEN (Select (Select top 1 ItemCode from RDR1 T3 where T3.docentry = T0.DocEntry and T3.TreeType = 'S' group by T3.ItemCode, T3.Visorder
Having Max(T3.Visorder) < T1.Visorder Order by T3.VisOrder desc))
ELSE T1.[ItemCode] END AS BOM
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[ItemCode]='51-6300' AND T1.[LineStatus] ='O'
ORDER BY T1.[ItemCode], T0.[DocDate]