Hey,
I have this query which mostly works. However for some of the records displayed (being duplicates) im getting an invoice number that comes from a completely different customer. From what I can see the query looks right but I cant figure out why some of them have extra rows with invoice numbers that arent theirs. Query below;
SELECT DISTINCT T0.CardCode, T0.Docnum[Delivery Num],T0.DocDate, T0.CardName[Customer],T1.ItemCode,T4.DistNumber, T1.WhsCode,T6.DocNum[Invoice Num] from ODLN T0 INNER join DLN1 T1 on T0.DocEntry=T1.DocEntry INNER join SRI1 I1 on T1.ItemCode=I1.ItemCode and (T1.DocEntry=I1.BaseEntry and T1.ObjType=I1.BaseType) INNER join OSRN T4 on T4.ItemCode=I1.ItemCode and I1.SysSerial=T4.SysNumber INNER JOIN INV1 T5 ON T5.BaseEntry = T1.DocEntry AND T5.BaseLine = T1.LineNum INNER JOIN OINV T6 ON T5.DocEntry = T6.DocEntry WHERE T1.ItemCode Like 'CW10%%' OR T1.ItemCode Like 'CD%%' OR T1.ItemCode Like 'CTL%%' OR T1.ItemCode Like 'CS%%' ORDER BY T0.DocDate DESC