looping over a query

[TESS_GEAR]

anyone helps me to loop over a query name, "FindUpperLvl".
Once "getInfo" query does not have any record, it will excute the "FindUpperLvl" query and FindUpperLvl query should loop until it finds one record.
The range of getInfo.levelid is 144 to 147.

Sorry I should explain more in detail.
The number of record in FindUpperLvl will be determined based on the getInfo query. The list of value getInfo.levelid can be 144, 145, 146, or 147.
If I found no record, I need to loop over again until it finds a record.
So for exmple, getInfo.levelid is 146 and there is no record found in FindUpperLvl, I need to pass 145 (getInfo.levelid ) which is less than the previous value. 144 is the smallest value and 147 is the biggest value I can pass.

[Guest]

Maybe I'm missing something, but if you loop over FindUpperLvl as it is currently coded, you will continue to get the same results because nothing is changing in the query. You have to change something in your WHERE clause each time you go through the loop.

[TESS_GEAR]

Sorry I should explain more in detail.
The number of record in FindUpperLvl will be determined based on the getInfo query. The list of value getInfo.levelid can be 144, 145, 146, or 147.
If I found no record, I need to loop over again until it finds a record.
So for exmple, getInfo.levelid is 146 and there is no record found in FindUpperLvl, I need to pass 145 (getInfo.levelid ) which is less than the previous value. 144 is the smallest value and 147 is the biggest value I can pass.

[insuractive]

Use ValueList(getInfo.levelid) to get a list of your levels, then perform a query that does something like this:

<cfset sLevelList = ValueList(getInfo.levelid)>

<cfquery name="FindUpperLvl " datasource="MyDataSource">
SELECT top 1 *
FROM myOtherTable
WHERE level_id in (<cfqueryparam cfsqltype="CF_SQL_INTEGER" list="Yes" value="#sLevelList #")
ORDER BY level_id desc
</cfquery>

That sould return the first record that matches the highest number in your getInfo.levelid query. You might have to do some editing to get it to work in the manner that you want, but it should point you in the right direction.

[Newsgroup_User]

how is it you expect the FindUpperLvl query to return anything if it is
conditioned to getInfo query when getInfo.recordcount eq 0???

--
Azadi Saryev
Sabai-dee.com
Vientiane, Laos
http://www.sabai-dee.com

[TESS_GEAR]

The value of getInfo.levelid determines by a user's input, so it should be always one value to pass to FindUpperLvl query. what I need to do... loop FindUpperLvl query until it found one record to stop the loop.
If FindUpperLvl query does not fetch any data with 148 (getInfo.levelid:this value comes first by a user), then it will pass 147 and tries to fetch a records. If it does not have a record with 147, it pass 146 and tries to fetch a records until 144. I need this as a loop outside of FindUpperLvl query.
so,
<cfset variablse.level = getInfo.levelid>
<cfloop condition="FindUpperLvl.recordcount EQ 0">
<cfset variables.level = variables.level - 1>
excute the same FindUpperLvl query again here (?)
</cfloop>