ColdFusion bug or feature? < Eddie Awad’s Blog

ColdFusion bug or feature?

Consider this simple ColdFusion code (CFMX Ent 7.0.1.116466 and Oracle DB 8.1.7.4):

<cfquery name="q1" datasource="#dsn#">
    select rownum recnum
    from all_objects
    where rownum <= 3
</cfquery>

<cfloop query="q1">

    <cfquery name="q2" datasource="#dsn#">
        select rownum recnum
        from all_objects
        where rownum <= 3
    </cfquery> 

    Outside q2 loop: <cfoutput>#q1.recnum#</cfoutput>
    <br>

    <cfloop query="q2">
        Inside q2 loop: <cfoutput>#q1.recnum#</cfoutput>
        <br>
    </cfloop>
    <br>
</cfloop>

In this example, both q1 and q2 return three rows each (it does not matter which tables you use). Each row has one column, recnum, with a value of 1, 2, and 3. Now, I thought that the above code would output the following:

Outside q2 loop: 1
Inside q2 loop: 1
Inside q2 loop: 1
Inside q2 loop: 1

Outside q2 loop: 2
Inside q2 loop: 2
Inside q2 loop: 2
Inside q2 loop: 2

Outside q2 loop: 3
Inside q2 loop: 3
Inside q2 loop: 3
Inside q2 loop: 3

But it did not. Instead, here is what I got:

Outside q2 loop: 1
Inside q2 loop: 1
Inside q2 loop: 1
Inside q2 loop: 1

Outside q2 loop: 2
Inside q2 loop: 1
Inside q2 loop: 1
Inside q2 loop: 1

Outside q2 loop: 3
Inside q2 loop: 1
Inside q2 loop: 1
Inside q2 loop: 1

Why is it that q1.recnum is stuck on the first row of q1 inside the q2 loop? What am I missing?

Tagged cfloop | Comments Closed | Trackbacks Closed

Comments (4)

Raymond Camden wrote:
Inside q2, when CF “talks” to the q1 query,it is NOT in the contest of cfoutput query=q1 anymore. Therefore it acts the same as

q1.columnname[1]
April 5th, 2006, at 8:55 am #
Barney wrote:
To put Ray’s comment another way: only the innermost query is ever implicitly referenced, all other queries must be referenced with full name.column[row] notation, and as you know, leaving off the row uses an implicit 1.
April 5th, 2006, at 9:23 am #
Ryan Guill wrote:
I had this same problem not long ago. I posted it to cf-talk if you want to see the discussion on it. If you cant find it let me know.

But what you need to make it work is instead of

Inside q2 loop: #q1.recnum#

use
```
<cfloop query="q2">
    Inside q2 loop: <cfoutput>#q1.recnum[q1.currentRow]#</cfoutput>
    <br>
</cfloop>
```
The current row is always correct where the loop gets messed up. This will force it to be correct.
April 5th, 2006, at 9:52 am #
Eddie Awad wrote:
Thanks guys for the clarification. By looking at the code alone, you would not think that q1.recnum in the q2 loop is in fact implicitly treated as q1.recnum[1]. Testing is important.

To solve this issue, I copied q1.recnum to the variables scope and used the variable inside the q2 loop:

Inside q2 loop: #variables.recnum#

But now I know that there is another way: q1.recnum[q1.currentRow]

Thanks again.

April 5th, 2006, at 12:35 pm #