Home » SQL & PL/SQL » SQL & PL/SQL » double recursion
double recursion Mon, 15 February 2010 10:10
 joshua82 Messages: 31Registered: 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 #443416 is a reply to message #443415] Mon, 15 February 2010 10:12
 BlackSwan Messages: 25478Registered: January 2009 Location: SoCal Senior Member
>but how can I do that with PL/SQL?

PL/SQL supports recursive functions, so start writing one.
Re: double recursion [message #443428 is a reply to message #443415] Mon, 15 February 2010 12:39
 joy_division Messages: 4755Registered: 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 #443447 is a reply to message #443415] Mon, 15 February 2010 17:11
 BlackSwan Messages: 25478Registered: January 2009 Location: SoCal Senior Member
I am not convinced that recursion is needed for the solution.
It appears that simple nested LOOP might suffice.
Re: double recursion [message #443449 is a reply to message #443415] Mon, 15 February 2010 19:22
 Kevin Meade Messages: 2102Registered: December 1999 Location: Connecticut USA Senior Member
If nested loop turns out to be sufficient then just use a row generator and do a single insert statement for the whole ball of wax.

Kevin
Re: double recursion [message #443450 is a reply to message #443415] Mon, 15 February 2010 20:00
 BlackSwan Messages: 25478Registered: January 2009 Location: SoCal Senior Member
>I need to extract 64 correlation:
64? Why 64?
last I checked 9*7 = 63; based upon posted sample data.

Re: double recursion [message #443507 is a reply to message #443415] Tue, 16 February 2010 01:48
 joshua82 Messages: 31Registered: 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: 5933Registered: 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
/```
 Previous Topic: select date values from a table between two dates (merged) Next Topic: variables in this loop procedure
Goto Forum:

Current Time: Wed Jun 28 14:30:47 CDT 2017

Total time taken to generate the page: 0.10747 seconds