on 05-23-2017 8:59 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
109 | |
15 | |
10 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.