get the record where the amount columns are same in 2 tables [message #684660] |
Sun, 18 July 2021 05:27 |
|
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I have data like this in the tables.
create table XXC_RECEIPT_TMP
as
select 111 APPLIED_CUSTOMER_TRX_ID, 1010 cash_receipt_id , 100 applied_amount from dual
union all
select 222 APPLIED_CUSTOMER_TRX_ID, 2020 cash_receipt_id , 100 applied_amount from dual
union all
select 333 APPLIED_CUSTOMER_TRX_ID,3030 cash_receipt_id , 100 applied_amount from dual
union all
select 444 APPLIED_CUSTOMER_TRX_ID, 3030 cash_receipt_id , 100 applied_amount from dual;
create table XXC_INVOICE_TMP
as
select 111 CUSTOMER_TRX_ID, 100 amount from dual
union all
select 222 CUSTOMER_TRX_ID, 100 amount from dual
union all
select 333 CUSTOMER_TRX_ID, 100 amount from dual
union all
select 444 CUSTOMER_TRX_ID, 100 amount from dual;
the join b/w tables are CUSTOMER_TRX_ID,APPLIED_CUSTOMER_TRX_ID.
1010,1001,2020 cash_receipt_id has single APPLIED_CUSTOMER_TRX_ID values and SUM OF amount and applied_amount is SAME and
I don't want to bring 3030 cash_receipt_id since it has APPLIED_CUSTOMER_TRX_ID 2 different values.
it is a little bit EMERGENCY so that is why I am posting the proposed solution here from the Oracle forum.
WITH T AS (
SELECT R.*,
COUNT(*) OVER(PARTITION BY R.CASH_RECEIPT_ID) CNT
FROM XXC_RECEIPT_TMP R
)
SELECT T.APPLIED_CUSTOMER_TRX_ID,
T.CASH_RECEIPT_ID,
T.APPLIED_AMOUNT
FROM T,
XXC_INVOICE_TMP I
WHERE T.APPLIED_CUSTOMER_TRX_ID = I.CUSTOMER_TRX_ID
AND T.CNT = 1
GROUP BY T.APPLIED_CUSTOMER_TRX_ID,
T.CASH_RECEIPT_ID,
T.APPLIED_AMOUNT
HAVING T.APPLIED_AMOUNT = SUM(I.AMOUNT)
When I run the above query with sample tables it's fine but when I use my original tables CNT is 11.
SELECT rcpt.APPLIED_CUSTOMER_TRX_ID,
rcpt.CASH_RECEIPT_ID,
rcpt.AMOUNT_APPLIED,
rcpt.CNT
FROM (SELECT R.APPLIED_CUSTOMER_TRX_ID,r.CASH_RECEIPT_ID,r.AMOUNT_APPLIED,
COUNT(*) OVER(PARTITION BY R.CASH_RECEIPT_ID) CNT
FROM ar_receivable_applications_all R
WHERE APPLICATION_TYPE='CASH') rcpt,
AAAR_FULL_APPLIED_INV rct,
ra_customer_trx_lines_all rctl
WHERE rcpt.APPLIED_CUSTOMER_TRX_ID = rct.CUSTOMER_TRX_ID
AND rct.CUSTOMER_TRX_ID=rctl.CUSTOMER_TRX_ID
AND rct.CUSTOMER_TRX_ID=7310493
--AND rcpt.CNT = 1
GROUP BY rcpt.APPLIED_CUSTOMER_TRX_ID,
rcpt.CASH_RECEIPT_ID,
rcpt.AMOUNT_APPLIED,
rcpt.CNT
HAVING rcpt.AMOUNT_APPLIED = SUM(rctl.EXTENDED_AMOUNT)
Can you please help me?
Thank you
[Updated on: Sun, 18 July 2021 05:30] Report message to a moderator
|
|
|
|
|
|