205
*This post is locked for comments
Try the below code.
CREATE VIEW dbo.CheckRegRp2t
AS
SELECT dbo.PM20000.VENDORID, dbo.PM00200.VNDCHKNM, dbo.PM20000.DOCDATE,
dbo.PM20000.VCHRNMBR, dbo.PM20000.TRXDSCRN, dbo.SY03900.TXTFIELD,
dbo.SY03900.NOTEINDX
FROM dbo.PM20000
INNER JOIN dbo.SY03900
ON dbo.SY03900.NOTEINDX = dbo.PM20000.NOTEINDX
INNER JOIN dbo.PM00200
ON dbo.PM00200.VENDORID = dbo.PM20000.VENDORID
UNION ALL
SELECT dbo.PM30200.VENDORID, dbo.PM00200.VNDCHKNM, dbo.PM30200.DOCDATE,
dbo.PM30200.VCHRNMBR, dbo.PM30200.TRXDSCRN, dbo.SY03900.TXTFIELD,
dbo.SY03900.NOTEINDX
FROM dbo.PM30200
INNER JOIN dbo.SY03900
ON dbo.SY03900.NOTEINDX = dbo.PM30200.NOTEINDX
INNER JOIN dbo.PM00200
ON dbo.PM00200.VENDORID = dbo.PM30200.VENDORID
Note: If my answer solved your problem then mark it as a verified. Because this will helps to some others who having the same problem.
Hope this helps!!!
OK so probably last question just in case the ones upstairs might need it, what if I wanted to add another table in there lets say PM00200 what would the code be like?
Yes. Typo error, I have changed above code.
Note: If my answer solved your problem then mark it as a verified. Because this will helps to some others who having the same problem.
Good Luck!!!
Hey there!! at first there was an error regarding the dbo.PM30200.TXTFIELD, it showed an error since there was no TXTFIELD column inside dbo.PM30200 but ichanged it back to dbo.PM30200.SY03900 and it worked
Thanks a lot guys! Oh and uhm if ever I might encounter another problem haha I might reply back in here.
Try the below code,
CREATE VIEW dbo.CheckRegRp2t
AS
SELECT dbo.PM20000.VENDORID, dbo.PM20000.DOCDATE,
dbo.PM20000.VCHRNMBR, dbo.PM20000.TRXDSCRN, dbo.SY03900.TXTFIELD,
dbo.SY03900.NOTEINDX
FROM dbo.PM20000
INNER JOIN dbo.SY03900
ON dbo.SY03900.NOTEINDX = dbo.PM20000.NOTEINDX
UNION ALL
SELECT dbo.PM30200.VENDORID, dbo.PM30200.DOCDATE,
dbo.PM30200.VCHRNMBR, dbo.PM30200.TRXDSCRN, dbo.SY03900.TXTFIELD,
dbo.SY03900.NOTEINDX
FROM dbo.PM30200
INNER JOIN dbo.SY03900
ON dbo.SY03900.NOTEINDX = dbo.PM30200.NOTEINDX
Hope this helps!!!
I have a new problem, Im studying your code and its confusing, sorry im a beginner in GP and SQL so I now have a table that displays
VENDORID / DOCDATE / VCHRNMBR / TRXDSCRN / TXTFIELD / NOTEINDX
all of them came from PM20000 and SY03900 and this is my code
CREATE VIEW dbo.CheckRegRp2t
AS
SELECT dbo.PM20000.VENDORID, dbo.PM20000.DOCDATE,
dbo.PM20000.VCHRNMBR, dbo.PM20000.TRXDSCRN, dbo.SY03900.TXTFIELD,
dbo.SY03900.NOTEINDX
FROM dbo.PM20000
INNER JOIN dbo.SY03900
ON dbo.SY03900.NOTEINDX = dbo.PM20000.NOTEINDX
but I want it to also include the data on PM30200 including its VENDORID, DOCDATE, VCHRNMBR, TRXDSCRN into one table. I don't want to put another column with the same name, I just want the data to go into the columns I already made
Hi, the below code is used to pull all the Payables & Payments(Work, Open & Hist) transactions with Note index details.
SELECT * FROM
(SELECT
PT.[Vendor ID],
VM.VNDCHKNM,
PT.[Voucher Number],
PT.[Document Type],
PT.[DOCUMENT DATE],
PT.[Document Amount],
PT.[TRX Source],
PT.[PPS Amount Deducted],
NM.TXTFIELD
--APPLDAMT, APFRDCNM, APTVCHNM
FROM PayablesTransactions PT --PAYABLES TRANSACTIONS WORK/OPEN/HIST
LEFT OUTER JOIN PM00200 VM --VENDOR MASTER
ON PT.[Vendor ID]= VM.VENDORID
LEFT OUTER JOIN SY03900 NM --RECORD NOTE MASTER
ON NM.NOTEINDX = PT.[NOTE INDEX]
UNION ALL
SELECT
MPW.VENDORID [Vendor ID],
VM.VNDCHKNM,
MPW.VCHRNMBR [Voucher Number],
'Payment',
MPW.DOCDATE [DOCUMENT DATE],
MPW.DOCAMNT [Document Amount],
MPW.TRXDSCRN [TRX Source],
MPW.PPSAMDED [PPS Amount Deducted],
NM.TXTFIELD
FROM PM10400 MPW --MANUAL PAYMENT WORK
LEFT OUTER JOIN PM00200 VM --VENDOR MASTER
ON MPW.VENDORID = VM.VENDORID
LEFT OUTER JOIN SY03900 NM --RECORD NOTE MASTER
ON NM.NOTEINDX = MPW.NOTEINDX) PAYABLES
Note: To get the values for the column(APPLDAMT, APFRDCNM & APTVCHNM), join the tables PM10200 & pm30300 to the end of the above query.
Please let me know if you need any other details.
Hope this helps!!!
I found it sir, thanks, now how do you make an SQL Query in which it shows the data of VNDCHKNM, VCHRNMBR, DOCDATE, DOCAMNT, TRXDSCRN, PPSAMDED, TXTFIELD, APPLDAMT, APFRDCNM, APTVCHNM in one table in which it shows all of transactions and its notes alongside it in order, this might be off topic but I hope somebody helps
SY03900 in the Company database. You'll have to link the Note Index column in SY03900 to the Note Index in the payables tables.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (