Skip to Content
0
May 29, 2020 at 10:20 AM

Extra rows that belong to another record

54 Views

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