Skip to Content
author's profile photo Former Member
Former Member

Doubled result on Query

Dear Experts,

Can anyone tell what is wrong with my query?

The original query gave me the correct result i needed, then when I added the CRD1 table 2 of the invoices got doubled.

SELECT 'IN' "IN/CN",T0.[DocNum],T2.[GroupName], T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T3.[Dscription], T3.[Quantity], T3.[LineTotal], T0.[PaidToDate], (T0.[DocTotal]-T0.[PaidToDate]) AS "Balance", T0.[Comments], T6.[Building]

FROM OINV T0

INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode

INNER JOIN INV1 T3 ON T0.DocEntry = T3.DocEntry

INNER JOIN OITM T4 ON T3.ItemCode = T4.ItemCode

INNER JOIN OITB T5 ON T4.ItmsGrpCod = T5.ItmsGrpCod

INNER JOIN CRD1 T6 ON T1.CardCode = T6.CardCode

WHERE T5.[ItmsGrpNam] =[%0] AND T0.[DocDate] BETWEEN [%1] and [%2] AND T0.[CardName] <> 'CASH SALES'

UNION ALL

SELECT 'CN', T0.[DocNum],T2.[GroupName], T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T3.[Dscription], -T3.[Quantity], -T3.[LineTotal], -T0.[PaidToDate], -(T0.[DocTotal]-T0.[PaidToDate] ) AS "Balance", T0.[Comments], T6.[Building]

FROM ORIN T0

INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode

INNER JOIN RIN1 T3 ON T0.DocEntry = T3.DocEntry

INNER JOIN OITM T4 ON T3.ItemCode = T4.ItemCode

INNER JOIN OITB T5 ON T4.ItmsGrpCod = T5.ItmsGrpCod

INNER JOIN CRD1 T6 ON T1.CardCode = T6.CardCode

WHERE T5.[ItmsGrpNam] =[%0] AND T0.[DocDate] BETWEEN [%1] and [%2] AND T0.[CardName] <> 'CASH SALES'

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Dec 10, 2015 at 12:45 PM

    Hi Carlo,

    The Table CRD1 contains multiple addresses for Business Partners. You will need to specify which address to use:

    e.g. INNER JOIN CRD1 T6 ON T1.CardCode = T6.CardCode and T6.LineNum = 0


    Richard

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 11, 2015 at 07:59 AM

    Try This

    SELECT 'IN' "IN/CN",T0.[DocNum],T2.[GroupName], T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T3.[Dscription], T3.[Quantity], T3.[LineTotal], T0.[PaidToDate], (T0.[DocTotal]-T0.[PaidToDate]) AS "Balance", T0.[Comments], T6.[Building]

    FROM OINV T0

    INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

    INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode

    INNER JOIN INV1 T3 ON T0.DocEntry = T3.DocEntry

    INNER JOIN OITM T4 ON T3.ItemCode = T4.ItemCode

    INNER JOIN OITB T5 ON T4.ItmsGrpCod = T5.ItmsGrpCod

    INNER JOIN CRD1 T6 ON T1.CardCode = T6.CardCode and T6.LineNum = 0

    WHERE T5.[ItmsGrpNam] =[%0] AND T0.[DocDate] BETWEEN [%1] and [%2] AND T0.[CardName] <> 'CASH SALES'

    UNION ALL

    SELECT 'CN', T0.[DocNum],T2.[GroupName], T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T3.[Dscription], -T3.[Quantity], -T3.[LineTotal], -T0.[PaidToDate], -(T0.[DocTotal]-T0.[PaidToDate] ) AS "Balance", T0.[Comments], T6.[Building]

    FROM ORIN T0

    INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

    INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode

    INNER JOIN RIN1 T3 ON T0.DocEntry = T3.DocEntry

    INNER JOIN OITM T4 ON T3.ItemCode = T4.ItemCode

    INNER JOIN OITB T5 ON T4.ItmsGrpCod = T5.ItmsGrpCod

    INNER JOIN CRD1 T6 ON T1.CardCode = T6.CardCode and T6.LineNum = 0

    WHERE T5.[ItmsGrpNam] =[%0] AND T0.[DocDate] BETWEEN [%1] and [%2] AND T0.[CardName] <> 'CASH SALES'

    Regards,

    Kennedy

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.