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>

[-__cfSearching__- · Accepted solution]

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.