Re: need help with recurrent select
In article <363314BE.167E@sni.de>,
Sabina Wahl <swahl.str@sni.de> wrote:
> hello,
>
Hello,
> i have in a table t two columns (keys) gozid and ozid and other columns.
> These 2 columns have a recurrent relationship. Following example will
> explain my problem:
>
> 1. step:
> select unique gozid from t where ozid = 0815;> results (f.e.): 1214,1218 (to store)
>
> now i have to search like that:
> select unique gozid from t where ozid = 1214 or ozid = 1218;>
> possible results: 2010,4030,4040 (to store)
>
> Now i have to select again like following:
> select unique gozid from t where ozid = 2010 or ozid = 4030 or ozid => 4040;
> Result: xy (to store)
>
> and so on until no result will be found anymore.
>
> 2. step:
>
> At least a select like that:
>
> select * on table t where <conditions> and (ozid = 1214 or ozid = 1218
> or ozid = 2010 .....)>
> Questions:
> Can i solve the first step with a recurrent stored procedure and how i
> should do this? Is there any solution for step 1 and step 2 within one
> select-statement/stored procedure ?
You will need some additional tables to save temporary results and to
use that results for next query. Actually two, because you can not
change table that you are using in subquery.
Let it be
create table temp1 ( zid int, level int );
insert into temp1 values ( 0815, 0 );
create table temp2 ( zid int, level int );
Then you can use stored procedure that in loop will
insert into temp select from t where ...
create procedure not_recurs ( ) returning int;begin
define i int;
define res int;
let i = 0;
while ( 1 = 1 )
select count(*) into res from temp1 where level = i;
if res = 0 then
exit while;
end if;
let i = i + 1;
insert into temp2
select zid, i from t1 where c2 in
( select zid from temp1 where level = i - 1 );
insert into temp1
select * from temp2 where level = i;
end while;
end
end procedure;
execute procedure not_recurs();
select gozid from t where ozid in ( select zid from temp2 );
This is not recursion. I don't think you will be able to do
this with some recursive procedure. Are you sure your data
will not lead you to infinite loop? By the way, you can
always check value of level column.
>
> Suggestions are welcome.
>
> Thanks
>
> --
> Sabina Wahl
> ASW PS12
> Email: swahl.str@sni.de
>
--
Vardan Aroustamian
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own