Query solution - oinv, inv1, inv3 - multiple lines created

[Former Member]

I'm trying to get a single line per invoice line, joining OINV, INV1 and INV3.   Unfortunately, the repeating lines pull in the tax and freight from INV3 multiple times

SELECT

  T0.[DocNum],

  T0.[DocStatus],

-- add doc type

  T0.[DocType],

  T0.[DocDate] as 'Posting Date',

  T0.[CardCode] AS 'Customer/Vendor Code',

  T0.[CardName] AS 'Customer/Vendor Name',

  T0.[NumAtCard] AS 'BP Reference No.',

  T0.DocCur,

  T0.[DocTotal] AS 'Document Total',

  T0.[PaidToDate] AS 'Paid to Date',

  T0.[FinncPriod] AS 'Posting Period',

  T0.[TotalExpns] AS 'Total Freight + Tax Charges',

  T0.[U_ARGNS_TAXSATAX] AS 'AvaTax Sales Tax',

  T5.[U_ARGNS_TAXTAXT] AS 'AvaTax Sales Tax FREIGHT',

  T0.[U_ARGNS_FGTCHR] AS 'Freight charge',

  T1.[LineNum]+1 AS 'Row Number',

  T1.[ItemCode] AS 'Item No.',

  T1.[Dscription] AS 'Item/Service Description',

  T1.[Quantity] AS 'Quantity',

  T1.[LineTotal] AS 'Row Total'

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

        INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

        INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

        INNER JOIN OACT T4 ON T1.AcctCode = T4.AcctCode

-- modified to LOJ from IJ

        Left Outer JOIN INV3 T5 ON T0.DocEntry = T5.DocEntry

WHERE  T0.[DocDate] Between [%0] and [%1]

        AND T3.ItmsGrpCod not in ('106', '107', '108', '109', '111', '120', '122')

        AND T2.ItemCode not in ('REGSHIP', 'RUSHSHIP')

        AND T0.DocType  = 'I'

        AND T0.DocCur = '$'

[Former Member · Accepted solution]

I found the answer.. I had to join as follows adding the T5.lineNum to T1.LineNum

        left outer JOIN INV3 T5 ON T0.DocEntry = T5.DocEntry

        and T5.LineNum = T1.LineNum

this is solved.

[amitrathi239]

Hi,

Might be you are getting more then one value because of left outer join.

Left Outer JOIN INV3

Check for one customer how many records are in the INV3 table.

Amit