Re: Recursive Cursor
> Article: 6167 of comp.databases.informix
> Path:
[Stuff deleted]
>
> The problem is you can not get what you want, if recursion is re-written
> into
> iteration. For example, a recursive call prints something in a format
> like
> begin_first_print ## 1st recursion
> begin_second_print ## 2nd recursion
> begin_third_print ## 3rd recursion
> ...
> end_third_print
> end_second_print
> end_first_print
>
> If changed to iteration, the result looks like
> begin_first_print ## 1st iteration
> end_first_print
> begin_second_print ## 2nd iteration
> end_second_print
> begin_third_print ## 3rd iteration
> end_third_print
> ...
>
Ok, so the problem becoms that not only do you want to process all the
records, you want to preserve the order in the output - note you do *not*
have to process the data in the required order as the report can be
ordered itself.
This is not a general solution, but might solve your problem depending on
circumstances. Add a number of integer fields to the temp table upto your
maximum expected depth of recursion e.g
create temp fred
(
r1 integer,
r2 integer,
r3 integer,
...rest of table
)
Then modify my original code as follows
function recursive_call()
let s = "select 0,0,0,* from any_table ",
"where any_field = ? ",
prepare cbTbl from s
execute cbTbl using any_value
let i = 1
open cbTbl using flds
foreach cbTbl into f.*
let f.r1 = i
insert into fred values ( f.* )
let i = i + 1
end foreach
declare c_cursor as
select * from fred
select count(*) into n from fred
while n != 0
foreach c_cursor into f.*
do case
when f.r3 != 0
let r = 3
when f.r2 != 0
let r = 2
otherwise
let r = 1
end case
if something_ok then
open cbTbl using flds
let i = 1
foreach cbTbl into f2.*
let f2.r1 = f.r1
let f2.r2 = f.r2
let f2.r3 = f.r3
do case
when r = 3
display "Recursion too deep..."
exit foreach
when r = 2
let f2.r3 = i
when r = 1
let f2.r2 = i
end case
insert into fred values ( f2.* )
let i = i + 1
end foreach
else
call do_else()
end if
end foreach
delete from fred
where current of c_cursor
select count(*) into n from fred
end while
free c_cursor
end function
> Furthermore, if function requires return value which is used in the
> function
> (most recursive functions do and that is the powerful point of the
> recursion),
> that means you can not finish first recursion until you get return value
> from
> second level of recursion, how could first iteration be finished and
> what value
> does first iteration generate? At leadt we know we can not get what we
> expect.
What you are describing is tail recursion which is the form of recursion
that can be easily transformed into iteration. In your example, as long
as the components of the calculation are passed to the report, the final
calculation can be left to the report itself.
>
> A suggestion for Informix is to make cursor as a true variable.
> (But it does not now. It is a symbol now. Even though you define it as a
> variable, it still works like a symbol. That is what I think.)
> When cursor is declared, informix checks if it is a variable. If it is
> not,
> let it work like it does right now. If it is, use the value of the
> variable
> as cursor name to declare cursor. So we can pass cursor name as a
> parameter
> to recursive function and different cursors under the same variable in
> different recursions can be declared. When close the cursor, it
> only close the cursor declared in current recursion.
>
> This is only a suggestion. Theoretically, it should work.
> But we can not make it work, only Informix can.
Yes, that would be a good idea!
> Tony Yang (714)771-0454
> Data Systems Support
> yang@dssmktg.com; bangpath - uunet.uu.net!dssmktg!yang
Hope this helps, it's not particularly elegant but you might be able to
adopt it to your situation.
Paul Hatcher
Graduate Associates Ltd +44 81 840 3110 - Phone
phatcher@cix.compulink.co.uk +44 81 567 1894 - Fax