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
