Skip to Content
avatar image
Former Member

Query to relate the sales orders and the client invoices

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.

query-related.png (38.4 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    May 24, 2017 at 06:03 AM

    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

    Add comment
    10|10000 characters needed characters exceeded