Dynamics Community Forum Thread Details

SQL Query - Duplicate Rows (Can't find unique link between table RM10101 and RM20101)

Here is a Query I pulled together. Overall goal is to export ALL Open transactions to import into a new company.

I see the issue is with the below from(I can't seem to find the right link to omit duplicate rows).

RIGHT OUTER JOIN RM10101 as dis ON dis.DOCNUMBR = inv.DOCNUMBR

Is there another table I should be using?

-----------------------------------------------------------------------------------------------------------------------

SELECT
   INV.CUSTNMBR as "Customer ID",
   v.custname as "Customer Name",
   CAST(INV.DOCDATE as DATE) as "DOC DATE",
   INV.SLPRSNID as "SLS Code",
   INV.SLSTERCD as "Terr Code",
   DIS.DistRef as "Distrib Ref",
   INV.DOCNUMBR as "DOC Number",
   DIS.DEBITAMT as "Debit Amt",
   DIS.CRDTAMNT as "Credit Amount",
   GL.ACTNUMST as "Account Num",
   DIS.DISTTYPE as "Distribution Type",
   INV.CSPORNBR as "P.O. Number",
   INV.TRXDSCRN as "Description",
   INV.RMDTYPal as "Transaction Type",
   INV.ORTRXAMT as "OMIT - Orig Amt",
   INV.CURTRXAM as "Replace With - Current Amt" --Separate Debit and Credits accordingly and confirm amount matches ARTB.

FROM RM20101 as INV --Open Transactions
   RIGHT OUTER JOIN RM10101 as dis ON dis.DOCNUMBR = inv.DOCNUMBR
   RIGHT OUTER JOIN gl00105 as gl ON gl.ACTINDX = dis.DSTINDX
   JOIN RM00101 as v ON v.CUSTNMBR = inv.CUSTNMBR
WHERE INV.CURTRXAM > '0' and inv.CUSTNMBR = 'ILDCFS427' and inv.docnumbr = 'L5661704-DEC16RB'
ORDER BY inv.CUSTNMBR, inv.DOCDATE, INV.DOCNUMBR, inv.TRXDSCRN