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 |
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 |
|
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.
|
|
|
|
Goto Forum:
Current Time: Tue Mar 19 02:22:25 CDT 2024
|