Skip to main content
G
gregmoser
Participant
January 4, 2010
Answered

Query Of Query Not Working

  • January 4, 2010
  • 1 reply
  • 261 views

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>

This topic has been closed for replies.
Correct answer -__cfSearching__-

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.

1 reply

-
-__cfSearching__-Correct answer
Inspiring
January 4, 2010

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices