title: CASE in Query of a Query
body: <p>In ColdFusion, after having queried an MSSQL Database and added a couple of rows with the <a href="https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/queryaddrow.html" rel="nofollow noreferrer">QueryAddRow</a> 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: </p>
  
  <pre><code>&lt;cfquery name="newquery" dbtype="query"&gt;
  SELECT * FROM query
  ORDER BY 
    CASE
      WHEN var LIKE 'All' THEN 'Zz'
      WHEN var LIKE '9%' THEN '0'
      ELSE var 
    END, year
  &lt;/cfquery&gt;
  </code></pre>
  
  <p>This returns an error: <code>Error Executing Database Query. Caught an exception, type = Database</code>. I've also attempted to use <a href="https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/queryexecute.html" rel="nofollow noreferrer">QueryExecute</a>. </p>
  
  <p>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 <code>(...) ORDER BY var, year</code> 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? </p>
  
  <p>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. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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. </p>
  
  <p>Please let me know if something is unclear. </p>