Hi everyone, I need to make a query that show me some info about an invoice and its related sales order. I already did the query and it shows the info in a "correct" way.
The problem is that I got repeated lines with the same info, what I need is someone who can help me to take off the extra lines. This is my query:
SELECT Distinct t2.DocNum 'Factura', t0.DocNum 'Orden',T2.CardName 'Cliente', t3.u_p_unitario 'Ordenado',T1.U_p_unitario 'Realizado',t1.Dscription, (t1.PriceBefDi*t1.rate) 'Costo' , t3.linetotal 'Subtotal Ordenado', t3.vatsum 'Impuestos',t3.GTotal ' $ Total Ordenado' , t1.linetotal 'Subtotal Realizado', t1.vatsum 'Impuestos',t1.GTotal ' $ Total Realizado' FROM ORDR T0 inner JOIN INV1 T1 ON T1.BaseEntry=T0.DocEntry AND T1.BaseType='17' inner JOIN OINV T2 ON T2.DocEntry=T1.DocEntry INNER JOIN RDR1 T3 on t0.DocEntry=t3.DocEntry Where T2.CardName='Toy Automotores S.A de C.V' order by t1.Dscription
And the result is this one:
As you can see. I got the same item like 2 or three times, with respect to the invoice and order numbers it doesn't matter that it is repeated.
I tried with "group by" but didn´t work or maybe I didn´t how to apply it.
In short, I only want to do not have the item / Description repeated.
Hi Anderson,
The problem is that in your query you have not linked the invoice and order lines. Please try this, and note that I only added the line link to the RDR1 join:
SELECT Distinct t2.DocNum 'Factura' , t0.DocNum 'Orden' , T2.CardName 'Cliente' , t3.u_p_unitario 'Ordenado' , T1.U_p_unitario 'Realizado' , t1.Dscription , (t1.PriceBefDi*t1.rate) 'Costo' , t3.linetotal 'Subtotal Ordenado' , t3.vatsum 'Impuestos' , t3.GTotal ' $ Total Ordenado' , t1.linetotal 'Subtotal Realizado' , t1.vatsum 'Impuestos' , t1.GTotal ' $ Total Realizado' FROM ORDR T0 INNER JOIN INV1 T1 ON T1.BaseEntry = T0.DocEntry AND T1.BaseType = '17' INNER JOIN OINV T2 ON T2.DocEntry = T1.DocEntry INNER JOIN RDR1 T3 on t0.DocEntry = t3.DocEntry AND T1.BaseLine = T3.LineNum WHERE T2.CardName = 'Toy Automotores S.A de C.V' ORDER BY t1.Dscription
Regards,
Johan
Thanks a lot, Johan!