You’re offline. This is a read only version of the page.
3,015
Is it possible to have someone help me add RM20101 to this query?
I specifically need CURTRXAM
SELECT * FROM
(
SELECT DATE1,APTODCNM,APPTOAMT,APFRDCNM, APFRDCDT,APTODCDT,TRXSORCE FROM RM20201
UNION ALL
SELECT DATE1,APTODCNM,APPTOAMT,APFRDCNM, APFRDCDT,APTODCDT,TRXSORCE FROM RM30201
) AS RM
LEFT OUTER JOIN
(
SELECT
Trx_Status,
TRXDATE Trx_Date,
JRNENTRY Journal_Entry,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
REFRENCE Reference,
SOURCDOC Source_Document,
ORTRXSRC Originating_TRX_Source,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
CURNCYID Currency_ID,
LASTUSER Last_User,
USWHPSTD User_Who_Posted
FROM
(SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
Trx_Status = 'Open', LASTUSER, USWHPSTD
FROM GL20000
WHERE SOURCDOC not in ('BBF','P/L')
AND VOIDED = 0
UNION ALL
SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
Trx_Status = 'History', LASTUSER, USWHPSTD
FROM GL30000
WHERE SOURCDOC not in ('BBF','P/L')
AND VOIDED = 0
UNION ALL
SELECT GD.ACTINDX, GH.TRXDATE, GH.SOURCDOC, GH.JRNENTRY,
GH.ORTRXSRC, GH.REFRENCE, GD.ORDOCNUM, GD.ORMSTRID, GD.ORMSTRNM,
GD.DEBITAMT, GD.CRDTAMNT, GH.CURNCYID, Trx_Status = 'Work',
LASTUSER, USWHPSTD
FROM GL10000 GH
INNER JOIN GL10001 GD
ON GH.JRNENTRY = GD.JRNENTRY
WHERE VOIDED = 0) GL
INNER JOIN GL00105 GM
ON GL.ACTINDX = GM.ACTINDX
INNER JOIN GL00100 GA
ON GL.ACTINDX = GA.ACTINDX
) AS GL
ON RM.APFRDCNM= GL.Originating_Doc_Number
*This post is locked for comments
I have the same question (0)
I don't see this as an applicable join, since RM20101 contains RM open documents. This join will result with "Null" Values, as the history documents are not joined.
Although, regardless of the logic behind this, to join this find the script below;
SELECT * FROM
(
SELECT DATE1,APTODCNM,APPTOAMT,APFRDCNM, APFRDCDT,APTODCDT,TRXSORCE FROM RM20201
UNION ALL
SELECT DATE1,APTODCNM,APPTOAMT,APFRDCNM, APFRDCDT,APTODCDT,TRXSORCE FROM RM30201
) AS RM
LEFT OUTER JOIN
(
SELECT DOCNUMBR,CUSTNMBR ,ORTRXAMT, CURTRXAM FROM RM20101
)AS RMO
ON RM.APTODCNM = RMO.DOCNUMBR
LEFT OUTER JOIN
(
SELECT
Trx_Status,
TRXDATE Trx_Date,
JRNENTRY Journal_Entry,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
REFRENCE Reference,
SOURCDOC Source_Document,
ORTRXSRC Originating_TRX_Source,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
CURNCYID Currency_ID,
LASTUSER Last_User,
USWHPSTD User_Who_Posted
FROM
(SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
Trx_Status = 'Open', LASTUSER, USWHPSTD
FROM GL20000
WHERE SOURCDOC not in ('BBF','P/L')
AND VOIDED = 0
UNION ALL
SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
Trx_Status = 'History', LASTUSER, USWHPSTD
FROM GL30000
WHERE SOURCDOC not in ('BBF','P/L')
AND VOIDED = 0
UNION ALL
SELECT GD.ACTINDX, GH.TRXDATE, GH.SOURCDOC, GH.JRNENTRY,
GH.ORTRXSRC, GH.REFRENCE, GD.ORDOCNUM, GD.ORMSTRID, GD.ORMSTRNM,
GD.DEBITAMT, GD.CRDTAMNT, GH.CURNCYID, Trx_Status = 'Work',
LASTUSER, USWHPSTD
FROM GL10000 GH
INNER JOIN GL10001 GD
ON GH.JRNENTRY = GD.JRNENTRY
WHERE VOIDED = 0) GL
INNER JOIN GL00105 GM
ON GL.ACTINDX = GM.ACTINDX
INNER JOIN GL00100 GA
ON GL.ACTINDX = GA.ACTINDX
) AS GL
ON RM.APFRDCNM= GL.Originating_Doc_Number
Share
Report
All responses (
Answers (
