CASE in Query of a Query

Question

In ColdFusion, after having queried an MSSQL Database and added a couple of rows with the QueryAddRow function, I need to reorder the query by doing a query of a query. The order that I need requires that I use the CASE expression within the ORDER BY clause, like so:

<cfquery name="newquery" dbtype="query">
SELECT * FROM query
ORDER BY 
  CASE
    WHEN var LIKE 'All' THEN 'Zz'
    WHEN var LIKE '9%' THEN '0'
    ELSE var 
  END, year
</cfquery>

This returns an error: Error Executing Database Query. Caught an exception, type = Database. I've also attempted to use QueryExecute.

If I query the database directly, the query works just fine, so I only get an error when I attempt to do the query of a query. If I remove the CASE expression and just do (...) ORDER BY var, year the query of a query also works just fine, so it seems to me that CASE is not possible in a query of a query. Is this correct, or should I be able to use CASE somehow?

Here's the structure that I need to reorder (after having added three rows). Obviously I've removed all columns not relevant to the problem at hand.

year | var 
2001 | 9-12
2002 | 9-12
2003 | 9-12
2001 | 12+
2002 | 12+
2003 | 12+
2001 | All
2002 | All
2003 | All
2000 | 9-12
2000 | 12+
2000 | All

As you can see, I'm using CASE to order the var column by a custom ordering. I'm open to suggestions that might circumvent the issue altogether.

Please let me know if something is unclear.

Answer 1

Query of Queries only supports certain things - case constructs appear to one of those things it does not.

My suggestion is to use QueryAddColumn to add a sortBy column to your query object. Then loop through this query and assign appropriate values to this column. Then sort by this column in your Q of Q.

Alternatively, you could simply do what you said you were able to do and incorporate the logic in the original database query.

Answer 2

• Create a column containing your CASE expression.
• Add this column before the asterisk
• use Order By 1

Answer 3

Not being familiar with MSSQL (I use Oracle), it looks like you're creating a dummy sort column in the ORDER BY clause. Why not create the dummy sort column in the SELECT clause of the original MSSQL query? That way it will be already available already in the QoQ without any additional logic. Of course you'd then need either CFCASE or CFIF code to handle your QueryAddRow() statement.

Your initial MSSQL query would look like something like this.

<cfquery name="query" datasource="MSSQL_DSN">
SELECT
    year,
    ...,
    var,
    CASE
        WHEN var LIKE 'All' THEN 'Zz'
        WHEN var LIKE '9%' THEN '0'
        ELSE var 
    END, myDummySortCol
</cfquery>

Your QueryAddRow() would look like something this.

<cfif varToAdd eq "All">
    <cfset QueryAddRow(query, {year="#yearToAdd#", ..., var="#varToAdd#", myDummySortCol="Zz"})>
<cfelseif Left(varToAdd, 1) eq "9">
    <cfset QueryAddRow(query, {year="#yearToAdd#", ..., var="#varToAdd#", myDummySortCol="0"})>
<cfelse>
    <cfset QueryAddRow(query, {year="#yearToAdd#", ..., var="#varToAdd#", myDummySortCol="#varToAdd#"})>
</cfif>

Your QoQ would look like this.

<cfquery name="newquery" dbtype="query">
SELECT * FROM query
ORDER BY myDummySortCol
</cfquery>