PL SQL Recursive Query

Please find below tables

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

TRANSACTION table
trx_id | debit | credit | type         | original_trx_id |    last_updated
1      | 0     | 0      | payment      |      null       |    25-JUL-11
2      | 0     | 2      | settlement   |        1        |    26-JUL-11
3      | 0     | 1      | settlement   |        1        |    27-JUL-11
4      | 3     | 0      | settlement   |        1        |    28-JUL-11
5      | 0     | 0      | payment      |      null       |    24-JUL-11
6      | 0     | 3      | settlement   |        5        |    25-JUL-11

RESULT EXPECTED:
trx_id | debit | credit | current_gross | current_net
2      | 0     | 2      | 10            | 12
3      | 0     | 1      | 12            | 13
4      | 3     | 0      | 12            |  9
6      | 0     | 3      | 10            | 13

Explanation

Transaction 1,2,3,4 falling into one set and transaction 5,6 falling into an another set. Each transaction set can be ordered using last updated column.

For the calculation we do not take the transactions type "payment". The "payment" transaction is linked to the event table. From where can find "original_gorss_amount" for calculation.

Steps

1. Find event table payment transaction from transaction table. ( Ex: transaction_id = 1,开发者_运维知识库 Also from that we can find original_gross_amount = 10 )
2. Take all the "settlement" transaction that has original_trx_id = 1
3. Order them based on last updated time.
4. Apply the calculation

Hope you have understood my question. I want to get the "RESULT EXPECTED" somehow using PL SQL ( Please no custom function)

I can not think a way to apply CONNECT BY here. Your help is highly appreciate.

Please find below create table and insert statements.

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,10,5);

 create table transaction
 (trx_id number(9),
  debit  number(9),
  credit number(9),
  type varchar2(50),
  original_trx_id number(9),
  last_updated DATE 
  );

insert into transaction values (1,0,0,'payment',null,'2011-07-25');
insert into transaction values (2,0,2,'settlement',1,'2011-07-26');
insert into transaction values (3,0,1,'settlement',1,'2011-07-27');
insert into transaction values (4,3,0,'settlement',1,'2011-07-28');
insert into transaction values (5,0,0,'payment',null,'2011-07-24');
insert into transaction values (6,0,3,'settlement',5,'2011-07-25');

If I understand you question right you don't want a hierarchial or recursive query. Just an analytic sum with a windowing clause.

SELECT  T1.trx_id
    ,   T1.debit
    ,   T1.credit
    ,   E2.gross_amount
        + NVL( SUM( T1.credit ) OVER( PARTITION BY T1.original_trx_id
                                 ORDER BY T1.last_updated
                                 RANGE BETWEEN UNBOUNDED PRECEDING
                                            AND 1 PRECEDING ), 0 )
        - NVL( SUM( T1.debit ) OVER( PARTITION BY T1.original_trx_id
                                 ORDER BY T1.last_updated
                                 RANGE BETWEEN UNBOUNDED PRECEDING
                                            AND 1 PRECEDING ), 0 )
                                AS current_gross
    ,   E2.gross_amount
        + SUM( T1.credit ) OVER( PARTITION BY T1.original_trx_id
                                 ORDER BY T1.last_updated
                                 RANGE BETWEEN UNBOUNDED PRECEDING
                                            AND CURRENT ROW )
        - SUM( T1.debit ) OVER( PARTITION BY T1.original_trx_id
                                 ORDER BY T1.last_updated
                                 RANGE BETWEEN UNBOUNDED PRECEDING
                                            AND CURRENT ROW )
                                AS current_net
FROM    g1_transaction  T1
    ,   g1_event        E2
WHERE   T1.original_trx_id  = E2.transaction_id
ORDER BY T1.original_trx_id, T1.last_updated

NOTE: A few of problems in your question (or at least my understanding of it).

• Should the 2nd insert into events set the gross_amount to be 12
• Should the current_gross of trx_id 4 in the results be 13 (instead of 12) because it includes the 1 credit from trx_id 3. And thus the net should be 10 (instead of 9)
• Should the current_gross of trx_id 6 be 12 (instead of 10) because this is the gross_amount of event 2. And thus the current_net would be 15 (instead of 13)

If these assumptions are correct then the query I provided gives these results.

    TRX_ID      DEBIT     CREDIT CURRENT_GROSS CURRENT_NET
---------- ---------- ---------- ------------- -----------
         2          0          2            10          12
         3          0          1            12          13
         4          3          0            13          10
         6          0          3            12          15