1 Reply Latest reply on Jan 4, 2010 1:40 PM by -==cfSearching==-

    Query Of Query Not Working

    gregmoser

      What is wrong with this query of query... Basically it seems like the "IF" logic in the select statement isn't looking at the current results, but instead the first line of the AllCategories() query.  Any ideas would be much appreciated.

       

       

          <cffunction name="getCategoryDetail" returntype="query" >
              <cfargument name="CategoryID" default="">
             
              <cfset AllCategories = Application.Celerant.QueriesCached.getAllCategories()>

       

              <cfquery name="CategoryDetail" dbtype="query" >
                  SELECT
                      *,
                      <cfif AllCategories.typ eq 'EMPTY'>
                          'DEPT' as ThisLevel,
                          AllCategories.Dept as ThisName
                      <cfelseif AllCategories.subtyp_1 eq 'EMPTY'>
                          'TYP' as ThisLevel,
                          AllCategories.typ as ThisName
                      <cfelseif AllCategories.subtyp_2 eq 'EMPTY'>
                          'SUBTYP_1' as ThisLevel,
                          AllCategories.subtyp_1 as ThisName
                      <cfelseif AllCategories.subtyp_3 eq 'EMPTY'>
                          'SUBTYP_2' as ThisLevel,
                          AllCategories.subtyp_1 as ThisName
                      <cfelse>
                          'SUBTYP_3' as ThisLevel,
                          AllCategories.subtyp_1 as ThisName
                      </cfif>
                  FROM
                      AllCategories
                  WHERE
                      AllCategories.web_taxonomy_id = #arguments.CategoryID#
              </cfquery>
             
              <cfreturn CategoryDetail>
          </cffunction>

        • 1. Re: Query Of Query Not Working
          -==cfSearching==- Level 4

          Basically it

          seems like the "IF" logic in the select statement isn't

          looking at the current results, but instead the first line

          of the AllCategories() query. 

           

          That is exactly what is happening. The IF is not evaluated within the QoQ. It is evaluated once, before the QoQ executes, using the values in the first record of the query.

           

          QoQ are very limited. AFAIK they and do not support that kind of logic. So you must loop through the query, one row at a time, and calculate the desired values.