Thanks to all.
HB> Normally, though, one would not have a reason to output rows
HB> (SUSPEND) from more than one level of nesting. Your results here
HB> would be (for me) very strange (and wrong).

So, I tried to give a short abstract of the problem situation I was
facing and successfully managed to leave out important info. I always
use the colon prefixes btw., just left them out of the abstract.

I narrowed down the problematic code structure to a turnkey just
containing what is necessary. Doing that, I found that it is _NOT_
connected with the total number of nested cursors.
But it obviously happens as soon as there is a second cursor on the
same table. Does _THAT_ probably sound like a known "no-no-neverdo"
which I have not been aware of?

The abstract of the turnkey is like this:
For
Select a.Field1, a.Field2 from TheTable a where a.x=:y into :V1, :V2
do
begin
-- send the main row to output
output1 = :V1;
output2 = :V2;
suspend;

-- look in same table for n rows belonging to main row
For
Select b.Field1, b.Field2 from TheTable b where b.IDProcess = :V2
into :V3, :V4
do
begin
output1 = :V3;
output2 = :V4;
suspend;
end
end

output1/output2 are the returning values/output parameters.

The error is in the values of output1/output2 in the 2nd cursor.
For each row of the 2nd cursor they do NOT have the values :V3/:V4 of
the inner cursor's position, but :V1/:V2 (of the outer cursor's
position)

All in all it is quite a complex structure and working fine in a lot
of reports for years. Now I needed that extension by including n rows
belonging to each row.
I had tried to avoid re-writing the whole beast (using UNIONed
streams) and just to hook in with an additional cursor for the same
table.

Well... I ended up rewriting it using UNIONed streams anyway now :)

ciao,
André


--


~~~Ihre Nachricht~~~

HB> At 10:35 PM 17/06/2009, you wrote:

>>Hi Folks,
>>
>>I still need to work with 1.5. (which also is a great server already,
>>btw!) due to legacy client code. Some more months and I'll be through,
>>hopefully.
>>
>>Anyway, today I tried for the first time ever to use a nested cursor
>>more than 2 levels deep.
>>
>>For
>>Select a.Field1, a.Field2 from ATable a into V1, V2
>>do
>>begin
>> For
>> Select b.Field1, b.Field2 from BTable b into V3, V4
>> do
>> begin
>> -- do something to generate output records
>> suspend;
>>
>> For
>> Select c.Field1, c.Field2 from CTable c into V5, V6
>> do
>> begin
>> -- do something to generate more records
>> suspend;
>> end
>> end
>>end
>>
>>(no "execute statement"s are included in this case).
>>
>>
>>After struggling to find the reason for very strange results, I
>>finally got the impression that results are just unpredictable as soon
>>as I am implementing the 3rd level, no matter what's in that 3rd
>>level.
>>
>>Can this be true?

HB> No.

>>Is that a known fact,

HB> No.

>>if so: is it no problem in 2.1.x?

HB> No.

>>Or is there no such problem even in 1.5.x

HB> No.

>>and my procedure must be wrong at some place?

HB> Yes. ;-)

HB> Why don't you tell us about your your expectations and
HB> your"very strange results"? Also show what the output set is meant
HB> to be (the RETURNS clause).

HB> Normally, though, one would not have a reason to output rows
HB> (SUSPEND) from more than one level of nesting. Your results here
HB> would be (for me) very strange (and wrong). The first row of
HB> output will have no value for V5 and V6; and then, after that,
HB> you are going to get lots and lots of rows that would be totally
HB> predictable if one had time to predict them; and the set would be
HB> chaotic and therefore quite useless.

HB> When you do finally get the loop logic right, don't forget to
HB> initialise your variables and then to reinitialise them at all the
HB> points where you need to.

HB> For v..5 I would also recommend that you do not omit the
HB> colon prefix on variables when you are referring to them in SQL.
HB> (I believe it is of less importance from v.2 onward.)

HB> ./hb



HB> ------------------------------------

HB> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

HB> Visit http://www.firebirdsql.org and click the Resources item
HB> on the main (top) menu. Try Knowledgebase and FAQ links !

HB> Also search the knowledgebases at http://www.ibphoenix.com

HB> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
HB> Yahoo! Groups Links



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH
Hafenweg 4
59192 Bergkamen-Rünthe

Telefon: +49 2389 9240 0
Telefax: +49 2389 9240 150
e-mail: info@...

Amtsgericht Hamm Nr. B 420 || USt-IDNr.: DE 125215402
Geschäftsführer: Achim Krähling, Dirk Salewski, Matthias Steinhaus