| Subject | Re: [firebird-support] nested cursors in 1.5.x |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-06-17T13:50:03Z |
At 10:35 PM 17/06/2009, you wrote:
Why don't you tell us about your your expectations and your"very strange results"? Also show what the output set is meant to be (the RETURNS clause).
Normally, though, one would not have a reason to output rows (SUSPEND) from more than one level of nesting. Your results here would be (for me) very strange (and wrong). The first row of output will have no value for V5 and V6; and then, after that, you are going to get lots and lots of rows that would be totally predictable if one had time to predict them; and the set would be chaotic and therefore quite useless.
When you do finally get the loop logic right, don't forget to initialise your variables and then to reinitialise them at all the points where you need to.
For v..5 I would also recommend that you do not omit the colon prefix on variables when you are referring to them in SQL. (I believe it is of less importance from v.2 onward.)
./hb
>Hi Folks,No.
>
>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?
>Is that a known fact,No.
>if so: is it no problem in 2.1.x?No.
>Or is there no such problem even in 1.5.xNo.
>and my procedure must be wrong at some place?Yes. ;-)
Why don't you tell us about your your expectations and your"very strange results"? Also show what the output set is meant to be (the RETURNS clause).
Normally, though, one would not have a reason to output rows (SUSPEND) from more than one level of nesting. Your results here would be (for me) very strange (and wrong). The first row of output will have no value for V5 and V6; and then, after that, you are going to get lots and lots of rows that would be totally predictable if one had time to predict them; and the set would be chaotic and therefore quite useless.
When you do finally get the loop logic right, don't forget to initialise your variables and then to reinitialise them at all the points where you need to.
For v..5 I would also recommend that you do not omit the colon prefix on variables when you are referring to them in SQL. (I believe it is of less importance from v.2 onward.)
./hb