Home » SQL & PL/SQL » SQL & PL/SQL » How to get recursive result (Oracle 11.2.0.1.0 - 64bit, Linux)
How to get recursive result [message #638135] Wed, 03 June 2015 15:52 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi,
I want to achieve recursive output from Oracle SQL. I tried few option like analytical functions like LAG or LAST_VALUE and I couldn't achieve correct result. While I'm trying, I want to post the same in our group incase anyone has better idea.

CREATE TABLE test_tt(a1 VARCHAR2(20), a2 VARCHAR2(20), b NUMBER, val NUMBER)
/


INSERT INTO test_tt VALUES('1140-1', NULL, null, 10);
INSERT INTO test_tt VALUES('1140-2', '1140-1', 10, NULL);
INSERT INTO test_tt VALUES('1140-3', '1140-1', 20, NULL);
INSERT INTO test_tt VALUES('1140-4', '1140-2', 30, NULL);
INSERT INTO test_tt VALUES('1140-5', '1140-3', 20, NULL);
INSERT INTO test_tt VALUES('1140-6', '1140-3', 10, NULL);
INSERT INTO test_tt VALUES('1140-7', '1140-4', 30, NULL);
INSERT INTO test_tt VALUES('1150-1', NULL, 0, 20);
INSERT INTO test_tt VALUES('1150-2', '1150-1', 11, NULL);
INSERT INTO test_tt VALUES('1150-3', '1150-1', 0, NULL);
INSERT INTO test_tt VALUES('1150-4', '1150-2', 11, NULL);

SQL> SELECT * FROM test_tt;
 
A1                   A2                            B        VAL
-------------------- -------------------- ---------- ----------
1140-1                                                       10
1140-2               1140-1                       10 
1140-3               1140-1                       20 
1140-4               1140-2                       30 
1140-5               1140-3                       20 
1140-6               1140-3                       10 
1140-7               1140-4                       30 
1150-1                                             0         20
1150-2               1150-1                       11 
1150-3               1150-1                        0 
1150-4               1150-2                       11 
 
11 rows selected



Expected output is as below

A1      A2      B     Val                 Logic
---------------------------
1140-1			 10
1140-2	1140-1	10	 20               B + Val of 1140-1(A2) in A1 i.e. 10 + 10 = 20
1140-3	1140-1	20	 30               B + Val of 1140-1(A2) in A1 i.e. 20 + 10 = 30
1140-4	1140-2	30	 50               B + Val of 1140-2(A2) in A1 i.e. 30 + 20 = 50
1140-5	1140-3	20	 50
1140-6	1140-3	10	 40
1140-7	1140-4	30	 90
1150-1           0	 20
1150-2	1150-1	11	 31
1150-3	1150-1	0        20
1150-4	1150-2	11	 42



Basically, I want the output to be calculated calculated recursively. Please let me know if you have any question.
Re: How to get recursive result [message #638136 is a reply to message #638135] Thu, 04 June 2015 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Recursive query:
SQL> with
  2    vals (a1, a2, b, val) as (
  3      select a1, a2, b, val from test_tt where a2 is null
  4      union all
  5      select tt.a1, tt.a2, tt.b,
  6             tt.b + nvl(v.val,0) val
  7      from vals v, test_tt tt
  8      where tt.a2 = v.a1
  9    )
 10  select * from vals
 11  order by a1, a2 nulls first
 12  /
A1                   A2                            B        VAL
-------------------- -------------------- ---------- ----------
1140-1                                                       10
1140-2               1140-1                       10         20
1140-3               1140-1                       20         30
1140-4               1140-2                       30         50
1140-5               1140-3                       20         50
1140-6               1140-3                       10         40
1140-7               1140-4                       30         80
1150-1                                             0         20
1150-2               1150-1                       11         31
1150-3               1150-1                        0         20
1150-4               1150-2                       11         42

11 rows selected.

Re: How to get recursive result [message #638160 is a reply to message #638136] Thu, 04 June 2015 09:10 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Thank you so much Michel...
Previous Topic: Show the total of records with last record
Next Topic: Invalid table alias with left join behaves differently on different versions
Goto Forum:
  


Current Time: Tue Mar 19 02:22:25 CDT 2024