Recursive PL SQL Query Help

Question

I have a two table. I want to get the below result out from these two table. Any help appreciate.

EVENT table

event_id | gross_amount | transaction_id
1        | 10           | 1
2        | 12           | 5

TRANSACTION table

trx_id | debit | credit | link_trx_id
1      | 4     | 0      | null
2      | 0     | 2      | 1
3      | 0     | 1      | 2
4      | 3     | 0      | 3
5      | 0     | 5      | null
6      | 0     | 3      | 5

RESULT EXPECTED:

trx_id | debit | credit | current_gross | current_net
1      | 4     | 0      | 10            | 6
2      | 0     | 2      | 6             | 8
3      | 0     | 1      | 8             | 9
4      | 3     | 0      | 9             | 6
5      | 0     | 5      | 10            | 15
6      | 0     | 3      | 15            | 18

Explanation

As you can see transaction 1,2,3,4 falling into an one set while 4,6 falling into an another set. For the each transaction needed the it's linked previous transactions current_net value as a it's current_gross.

Basically getting current_gross is a recursive call. Here I can not use PL SQL function where I can write a quick recursive function to calculate current_gross. I need pure PL/SQL query for this task. ( Can use built in PL SQL functions)

Allan · Accepted Answer · 2011-07-29 18:31:47Z

2

This is an untested, approximate answer (if you had provided create table and insert statements for your data/structure, I would have tested it). It assumes that there is no branching in your transaction table (that is, that link_trx_id is unique). Basically, we use a recursive join to get the grouping, parent information, and order, then use analytic functions to get a running total (current_net). Rather than try to get the previous total (which we could do), it seems to make more sense to simply remove the current row's credit and debit from the current_net.

SELECT trx_id,
       debit,
       credit,
       root_amt - cum_debit + cum_credit + debit - credit AS current_gross,
       root_amt - cum_debit + cum_credit AS current_net
FROM   (SELECT trx_id,
               debit,
               credit,
               SUM(credit) OVER (PARTITION BY event_id ORDER BY lvl) AS cum_credit,
               SUM(debit) OVER (PARTITION BY event_id ORDER BY lvl) AS cum_debit,
               root_amt
        FROM   (SELECT     trx_id,
                           debit,
                           credit,
                           LEVEL AS lvl,
                           CONNECT_BY_ROOT (gross_amount) AS root_amt,
                           CONNECT_BY_ROOT (event_id) AS event_id
                FROM       transaction t LEFT OUTER JOIN event e ON t.trx_id = e.transaction_id
                CONNECT BY link_trx_id = PRIOR trx_id
                START WITH link_trx_id IS NULL))

edited Jul 29, 2011 at 18:31

answered Jul 28, 2011 at 20:00

Allan

17.5k5 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

era Over a year ago

It's my fault not providing inserts and create table statements. Thanks Jeff has corrected the query.

Jeff Moore · Accepted Answer · 2011-07-29 00:57:52Z

Using Allan's query, I added the create table and inserts. The query had mismatched variables so I correct those as well (debit_cum/credit_cum did not match the cum-credit/cum_debit variables in the sub-query).

create table event
(event_id   number(9),
 gross_amount number(9),
 transaction_id number(9));

 insert into event values (1,10,1);
 insert into event values (2,12,5);

 create table transaction
 (trx_id number(9),
  debit  number(9),
  credit number(9),
  link_trx_id number(9)
  );

  insert into transaction values (1,4,0,null);
  insert into transaction values (2,0,2,1);
  insert into transaction values (3,0,1,2);
  insert into transaction values (4,3,0,3);
  insert into transaction values (5,0,5,null);
  insert into transaction values (6,0,3,5);



SELECT trx_id,
       debit,
       credit,
       root_amt - debit_cum + credit_cum + debit - credit AS current_gross,
       root_amt - debit_cum + credit_cum AS current_net
FROM   (SELECT trx_id,
               debit,
               credit,
               SUM(credit) OVER (PARTITION BY event_id ORDER BY lvl) AS credit_cum,
               SUM(debit) OVER (PARTITION BY event_id ORDER BY lvl) AS debit_cum,
               root_amt,
               event_id
        FROM   (SELECT     trx_id,
                           debit,
                           credit,
                           LEVEL AS lvl,
                           CONNECT_BY_ROOT (gross_amount) AS root_amt,
                           CONNECT_BY_ROOT (event_id) AS event_id
                FROM       transaction t LEFT OUTER JOIN event e ON t.trx_id = e.transaction_id
                CONNECT BY link_trx_id = PRIOR trx_id
                START WITH link_trx_id IS NULL));

Allan should get the credit for the correct query. He did all of the hard work.

Collectives™ on Stack Overflow

Recursive PL SQL Query Help

Explanation

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Explanation

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related