My query showing multiple records.

[Former Member]

I have following qurery:

SELECT DISTINCT T0."CardCode" as "Vendor Code",T0."CardName"as "Vendor Name", T6."U_SERIES" as "Series", T9."GroupName", T6."U_Industry",T7."descript" as "Territory", T0."DocNum", T0."DocDate",b."SeriesName",
T0."DocDueDate", T0."TaxDate", T1."ItemCode", T8."ItemName",T8."U_Extra2" as "Material Group",T8."U_Extra3" as "Material Group Description",T1."Quantity",T1."Price" , (T1."Quantity"* T1."Price")as "Value",T1."TaxCode",T1."VatSum",T1."GTotal"+T0."TotalExpns" as "Grand Total",
T5."Street",T5."City",T5."State",T5."Country" , T2."FirstName" as "Contact Person", T0."NumAtCard", T0."CurSource", T3."BPLName", T4."SlpName",T10."OnHand",T1."AcctCode",T0."TotalExpns",P2."DocNum" as "GRPO Doc No",P2."DocDate",P3."DocNum" as "PO Doc No",P3."DocDate"
FROM OPCH T0 INNER JOIN PCH1 T1 ON T0."DocEntry" = T1."DocEntry"
LEFT JOIN OCPR T2 ON T0."CntctCode" = T2."CntctCode"
LEFT JOIN OBPL T3 ON T0."BPLId" = T3."BPLId"
LEFT JOIN OSLP T4 ON T0."SlpCode" = T4."SlpCode"
LEFT JOIN OCRD T6 ON T0."CardCode" = T6."CardCode"
LEFT JOIN CRD1 T5 ON T0."CardCode" = T5."CardCode" AND T5."Address" = T6."ShipToDef"
LEFT JOIN OTER T7 ON T6."Territory" = T7."territryID"
LEFT JOIN OITM T8 ON T1."ItemCode"= T8."ItemCode"
LEFT JOIN OCRG T9 ON T6."GroupCode"=T9."GroupCode"
LEFT JOIN OITW T10 on T1."ItemCode" = T10."ItemCode" AND T1."WhsCode"=T10."WhsCode"
LEFT JOIN POR3 T11 ON T0."DocEntry" = T11."DocEntry"
LEFT OUTER JOIN "NNM1" b ON T0."Series" = b."Series"

LEFT OUTER JOIN PDN1 T12 ON T1."BaseEntry" = T12."DocEntry" AND T1."BaseLine"=T12."LineNum" AND T1."BaseType"='20'
LEFT OUTER JOIN OPDN P2 ON T12."DocEntry" = P2."DocEntry"

LEFT OUTER JOIN POR1 T13 ON T12."BaseEntry" =T13."DocEntry" AND T12."BaseLine"=T13."LineNum" AND T12."BaseType"='22' AND T12."ItemCode"=T13."ItemCode"
LEFT OUTER JOIN OPOR P3 ON T12."DocEntry" = P2."DocEntry"

WHERE T3."BPLName" = N'[%0]' AND T0."TaxDate" >= '[%1]' AND T0."TaxDate" <= '[%2]'
ORDER BY T0."DocNum" ASC

When i left Join with Table "POR1". It is showing multiple records.

Any

suggestion

?

[former_member312729]

Hi Ankur,

Replication of multiple records due to T5."Address" = T6."ShipToDef"

Instead of T5."Address" = T6."ShipToDef" on the line LEFT JOIN CRD1 T5 ON T0."CardCode" = T5."CardCode" AND T5."Address" = T6."ShipToDef" replace with

T0."PayToCode" = T6."Address"

[Former Member]

I don't think you are getting my question .

Till POR1 table ,query works totally fine. After i Join with POR1 , records get duplicated.

[former_member312729]

Hi Ankur,

Same query i am testing with the tables you had used. At CRD1 duplicate records were showing. Below is the query you can try. Kindly add the where condition and Order by, also add the fields which are left out from your above query

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T3.[FirstName], T4.[BPLName], T5.[SlpName], T6.[Street], T6.[City], T6.[Country], T6.[State], T7.[descript], T9.[GroupName], T10.[OnHand] FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode] INNER JOIN OCPR T3 ON T0.[CntctCode] = T3.[CntctCode] INNER JOIN OBPL T4 ON T0.[BPLId] = T4.[BPLId] INNER JOIN OSLP T5 ON T0.[SlpCode] = T5.[SlpCode] Inner JOIN CRD1 T6 on T0.[CardCode] = T6.[CardCode] and T0.[PayToCode] = T6.[Address] INNER JOIN OTER T7 ON T2.[Territory] = T7.[territryID] INNER JOIN OITM T8 ON T1.[ItemCode] = T8.[ItemCode] INNER JOIN OCRG T9 ON T2.[GroupCode] = T9.[GroupCode] INNER JOIN OITW T10 ON T8.[ItemCode] = T10.[ItemCode] and T1.WhsCode=T10.WhsCode INNER JOIN PDN1 T11 ON T8.[ItemCode] = T11.[ItemCode] and T1.BaseEntry = T11.DocEntry AND T1.BaseLine=T11.LineNum AND T1.BaseType='20' INNER JOIN OPDN T12 ON T11.[DocEntry] = T12.[DocEntry] INNER JOIN POR1 T13 ON T8.[ItemCode] = T13.[ItemCode] and T11.BaseEntry = T13.DocEntry AND T11.BaseLine=T13.LineNum and  T11.BaseType='22'  INNER JOIN OPOR T14 ON T13.[DocEntry] = T14.[DocEntry] Inner JOIN NNM1 T15 ON T0.[Series] = T15.[Series]