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.
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.