Query Loop question

hi,

i have two sets of queries. on the first one, i retrieve some time information (getTimeInfo) and on the second one i retrieve specific task for day of the week information (getTask).

i need to compare the results of getTimeInfo to those of getTask line by line. so, I decided to use two <cfloop> statements. However when I use the following code, it just looks at the very first line of getTask and doesn't go to the next row of getTask.

<cfloop query="getTask">
<cfloop query="getTimeInfo ">
<cfset numOfBreaks = getTimeInfo.Length / breaklength>
<cfif getTimeInfo.breaktime GE getTask.StartTime AND getBreakTime.breaktime LE getTask.StopTime>

<cfif (ZoneID EQ 72) OR (ZoneID EQ 65) OR (ZoneID EQ 59)>
<cfif getTask.Monday EQ 1>
<cfloop from="1" to="#numOfBreaks#" index="i">
<cfset newId = newId + 1>
<cfquery datasource="Anatolia">
INSERT INTO .. .. VALUES ( ....) </cfquery>
</cfloop>
<cfelse>
<cfset newId = newId +1>
<cfquery datasource="Anatolia">
INSERT INTO ..... VALUES ( .....)
</cfquery>
</cfif>
<cfelse>
<cfif getTask.Monday EQ 0>
<cfset newId = newId + 1>
<cfquery datasource="Anatolia">
INSERT INTO .... VALUES (....)
</cfquery>
<cfelse>
<cfset newId = newId + 1>
<cfquery datasource="Anatolia">
INSERT INTO DenInt .... VALUES ....
</cfquery>
</cfif>
</cfif>
</cfif>
</cfloop>
</cfloop>

what should happen is the first line from the getTask is retrieved, the comparison is made and some number of records are inserted into a table. then second for of getTask and all the rest. but when i look at the table, i see the same line for so many times inserted. for some reason, the loop does not execute the second fetched row and so on. what am i doing wrong? thanks for the help in advance

I'd start by just doing some simply text output to confirm that the 2 query loops are working the way you expect. Then I'd make sure that the value of #numOfBreaks# is what you expect by outputting that.

thanks Kiteless,

I just did a little test as you suggested with two counters.
<Cfset i = 0>
<cfset k = 0>
<cfloop query="getTask">
<cfset i = i + 1>
<cfoutput>#i#</cfoutput>
<cfloop query="getTimeInfo">
<cfset k = k +1>
<cfoutput>#k#</cfoutput>
</cfloop>
</cfloop>

getTask returns 48 rows and getTimeInfo returns 168
the result shows that the i got stuck at 1 while k went from 1 to 48*168 = 8064.
i dont understand why it wouldnt go 1 1-168, then 2 1-168 then 3 1-168 and so on.
instead i have 1 1 2 3 4 5 6 7 8..... 8064

Let's examine and confirm:

<Cfset i = 0>
<cfset k = 0>
<cfoutput>
<cfloop query="getTask"> [48 rows]
<cfset i = i + 1>
i:#i#
<cfloop query="getTimeInfo"> [168 rows]
<cfset k = k +1>
k:#k#
</cfloop>
<br><br>
</cfloop>
</cfoutput>

First, put a note in front of each counter so you are sure you're looking at the right thing...see above

Based on what is above I would think you should get something like this:

i:1 k:1 k:2 k:3....k:168

i:2 k:1 k:2...k:168

i:3 k:1 k:2...etc.

Its a feature/bug in CFMX, that when nesting 2 cfloops and referencing an outerquery-row from the inner query, always the 1. row of the outer query is used. I don't know why that is, maybe you should google for it, if you are interested.
To solve the problem, you can either save the the desired column to a local variable in the outer loop(before starting the inner cfloop) and access it in the inner one. Alternativly you can access the variable directly, using array notification (e.g. GetTask.Monday[GetTask.CurrentRow]).

Yep, I'd totally forgotten about that. Using currentRow in your reference to the outer query's field names should get you the correct value.