You’re offline. This is a read only version of the page.
3,015
I need help writing a SQL join that does not produce duplicates. I need to combine these three tables. Your help will be greatly appreciated seeing as I can use your example as a future reference of learning.
View: AccountTransactions
View: customers
View: ReceivablesTransactions
I am using report builder 2.0. GP 2010.
*This post is locked for comments
Referring to Ms. Victoria's Blog, there is a script for all receivable transactions with associated definition for all the fields. Each transaction will have at least two of the following distribution types depending on the transaction type and associated parameters
RM Distribution Types;
SQL view with all GL distributions for AR transactions
I do believe that you need to get all Receivable transactions details for a specific distribution type (Account Receivable). To add a filter to the script above;
SELECT * FROM
(
SCRIPT
) AS RCVTRX
WHERE RCVTRX.Distribution_Type = 'Accounts Receivable'
Just to note that joining more than one distribution record for the receivable document will result with duplicating the header details as previously illustrated in another post of yours;
Add debit and credit from RM10101
Hope this helps,
What type of Account Transactions? GL transactions? and do you want to sort by customer or transaction?
here is one script keying off of the customer:
SELECT DISTINCT * FROM RM00101 C LEFT OUTER JOIN RM20101 T ON C.CUSTNUM=T.CUSTNUM
If by account transactions you mean the GL transactions for the receivable, then do another Left Outer Join on the RM distributions table, joining on the transaction IDs.
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,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (