My query to show transaction and receipt amounts by transaction are duplicating the transaction amounts when multiple receipts are applied to it.
I’m using select distinct and the sum over partition since a transaction might have multiple lines, and I only want to report the total transaction amount here.
select distinct
sum (rctl.revenue_amount) over (partition by rcta.trx_number) as "TRX Ammount",
acra.receipt_number as "Receipt Number"
from
apps.ra_customer_trx_all rcta,
apps.ra_customer_trx_lines_all rctl,
apps.ar_receivable_applications_all araa,
apps.ar_cash_receipts_all acra
where
rcta.customer_trx_id = rctl.customer_trx_id
and rcta.trx_number="12345"
and rcta.customer_trx_id = araa.applied_customer_trx_id
and araa.cash_receipt_id = acra.cash_receipt_id
When running the query for just the rcta and rctl tables, I get the expected value.
TRX Amount |
---|
5 |
When adding in the araa and acra receipt tables, the value is triple, since the transaction here has three receipts applied to it; I know I’ll get three rows with the same amount, but I want the amount to actually be correct.
TRX Amount | Receipt Number |
---|---|
15 | |
15 | Receipt2 |
15 | Receipt3 |
I feel like this is probably a basic question, please excuse me as I’m still new to SQL. From reviewing other threads it looks like it involves having a "From (select distinct" coding but I’m not sure how to do this when joining tables.
Thanks!
You need to sign in to view this answers