Home » SQL & PL/SQL » SQL & PL/SQL » double recursion
double recursion [message #443415] Mon, 15 February 2010 10:10 Go to next message
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 #443416 is a reply to message #443415] Mon, 15 February 2010 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
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 Go to previous messageGo to next message
joy_division
Messages: 4617
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 #443447 is a reply to message #443415] Mon, 15 February 2010 17:11 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: 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 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
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 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
Sorry, I post a wrong situation. Sad
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 Go to previous message
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
/
Previous Topic: select date values from a table between two dates (merged)
Next Topic: variables in this loop procedure
Goto Forum:
  


Current Time: Wed Sep 28 21:11:29 CDT 2016

Total time taken to generate the page: 0.07977 seconds