Home » SQL & PL/SQL » SQL & PL/SQL » get the record where the amount columns are same in 2 tables (11.2)
get the record where the amount columns are same in 2 tables [message #684660] Sun, 18 July 2021 05:27 Go to next message
mist598
Messages: 1194
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

Re: get the record where the amount columns are same in 2 tables [message #684661 is a reply to message #684660] Sun, 18 July 2021 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68005
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I could help but I'm blocked waiting for your feedback in your previous topics.

Re: get the record where the amount columns are same in 2 tables [message #684662 is a reply to message #684661] Sun, 18 July 2021 06:12 Go to previous messageGo to next message
mist598
Messages: 1194
Registered: February 2013
Location: Hyderabad
Senior Member
Ok i will do later
Re: get the record where the amount columns are same in 2 tables [message #684663 is a reply to message #684660] Sun, 18 July 2021 07:27 Go to previous message
EdStevens
Messages: 1344
Registered: September 2013
Senior Member
Quote:
I am posting the proposed solution here from the Oracle forum.
So if you posted the same question on a different forum, and are having a problem with a solution proposed on that forum, you should take that up with the person that proposed the solution --- on the other forum.
Previous Topic: Unable to get the PL/SQL block to work
Next Topic: How to stop swapping in EXCHANGE PARTITION
Goto Forum:
  


Current Time: Sun Oct 24 02:09:37 CDT 2021