Home » SQL & PL/SQL » SQL & PL/SQL » double recursion
| double recursion [message #443415] |
Mon, 15 February 2010 10:10  |
joshua82
Messages: 31
Registered: December 2009
|
Member |
|
|
Hi! I need to do a recursive correlation between column from a table like this:
CREATE TABLE ttemp(
val1 in NUMBER NOT NULL,
val2 in NUMBER NOT NULL,
val3 in NUMBER NOT NULL,
val4 in NUMBER NOT NULL,
val5 in NUMBER NOT NULL,
val6 in NUMBER NOT NULL,
val7 in NUMBER NOT NULL,
val8 in NUMBER NOT NULL
)
INSERT INTO ttemp VALUES(1, -3, 5, -2, 8, 6, 8, 9, 0);
INSERT INTO ttemp VALUES(2, 3, 8, 6, 4, 9, 3, 6, -7);
INSERT INTO ttemp VALUES(3, 2, 8, 9, 3, 4, 3, 7, 9);
INSERT INTO ttemp VALUES(4, 1, -4, 4, 5, 7, 9, 9, 5);
INSERT INTO ttemp VALUES(5, 7, 6, 2, -9, 7, 9, 9, 6);
INSERT INTO ttemp VALUES(6, 5, 2, 9, 6, 5, 8, 6, 8);
INSERT INTO ttemp VALUES(7, 4, 0, 2, 5, 9, 5, 8, 3);
I need to extract 64 correlation: corr(val1,val1),corr(val1,val2), corr(val1,val3), ... ,corr(val2,val1),corr(val2,val2), ... and so on , but I need to do that in a recursive form.
What I need is a kind of double for:
for i = 1..n (for j = 1..n do the correlation)
but how can I do that with PL/SQL?
thanks!
|
|
|
|
|
|
| Re: double recursion [message #443428 is a reply to message #443415] |
Mon, 15 February 2010 12:39  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
All of those insert statements will fail. You insert more values than columns in the table.
This is why it is important to name your columns in an insert.
|
|
|
|
|
|
|
|
|
|
| Re: double recursion [message #443507 is a reply to message #443415] |
Tue, 16 February 2010 01:48 |
joshua82
Messages: 31
Registered: December 2009
|
Member |
|
|
Sorry, I post a wrong situation. 
CREATE TABLE ttemp(
val1 in NUMBER NOT NULL,
val2 in NUMBER NOT NULL,
val3 in NUMBER NOT NULL,
val4 in NUMBER NOT NULL,
val5 in NUMBER NOT NULL,
val6 in NUMBER NOT NULL,
val7 in NUMBER NOT NULL,
val8 in NUMBER NOT NULL
)
INSERT INTO ttemp VALUES(1, -3, 5, -2, 8, 6, 8, 9);
INSERT INTO ttemp VALUES(2, 3, 8, 6, 4, 9, 3, 6);
INSERT INTO ttemp VALUES(3, 2, 8, 9, 3, 4, 3, 7);
INSERT INTO ttemp VALUES(4, 1, -4, 4, 5, 7, 9, 9);
INSERT INTO ttemp VALUES(5, 7, 6, 2, -9, 7, 9, 9);
INSERT INTO ttemp VALUES(6, 5, 2, 9, 6, 5, 8, 6);
INSERT INTO ttemp VALUES(7, 4, 0, 2, 5, 9, 5, 8);
I need 63 correlation.
It's true, probably a nested loop might be a solution: but how can I loop through different column?
|
|
|
|
| Re: double recursion [message #443519 is a reply to message #443415] |
Tue, 16 February 2010 02:10 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Notwithstanding the fact that there was an error in almost every single line of your solution, here's a better way of diing it than Pl/Sql:select x1.no
,x2.no
,corr(case when x1.no = 1 then t.val1
when x1.no = 2 then t.val2
when x1.no = 3 then t.val3
when x1.no = 4 then t.val4
when x1.no = 5 then t.val5
when x1.no = 6 then t.val6
when x1.no = 7 then t.val7
when x1.no = 8 then t.val8
end
,case when x2.no = 1 then t.val1
when x2.no = 2 then t.val2
when x2.no = 3 then t.val3
when x2.no = 4 then t.val4
when x2.no = 5 then t.val5
when x2.no = 6 then t.val6
when x2.no = 7 then t.val7
when x2.no = 8 then t.val8
end) cor
from ttemp t
,(select level no from dual connect by level <=8) x1
,(select level no from dual connect by level <=8) x2
group by x1.no,x2.no
/ |
|
|
|
Goto Forum:
Current Time: Tue Feb 11 16:59:01 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
