5 Replies Latest reply on Apr 2, 2007 11:30 AM by Newsgroup_User

    looping over a query

    TESS_GEAR Level 1
      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.
        • 1. Re: looping over a query
          jdeline Level 1
          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.
          • 2. Re: looping over a query
            TESS_GEAR Level 1
            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.
            • 3. looping over a query
              insuractive Level 3
              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.
              • 4. Re: looping over a query
                Level 7
                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
                • 5. Re: looping over a query
                  TESS_GEAR Level 1
                  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>