title: CASE in MSSQL WHERE clause - odbc error
body: <p>In my PHP web application I am trying to perfect logic that, a user-defined value, assembles a Transact-SQL query which filters on that value. The query is then executed using the ODBC driver. The complication is that the filtering will only be done on derived fields. This is working absolutely fine except for when the derived field is a field created using a CASE expression.</p>
  
  <p>So, for example, I have a derived field such as the following:</p>
  
  <pre><code>CASE WHEN [text_result] IS NOT NULL THEN [text_result] ELSE
      CASE WHEN [last_event] = 1 THEN 'processed' ELSE 'unprocessed' END
  END
  </code></pre>
  
  <p>If I try and filter this using the value "unprocessed" then the final assembled query is obviously something akin to the following:</p>
  
  <pre><code>SELECT * FROM table WHERE 
      CASE WHEN [text_result] IS NOT NULL THEN [text_result] ELSE
          CASE WHEN [last_event] = 1 THEN 'processed' ELSE 'unprocessed' END
      END = 'unprocessed'
  </code></pre>
  
  <p>However when this runs I receive the following error:</p>
  
  <pre><code>Warning: odbc_execute(): SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'from'., SQL state 37000 in SQLDescribeParameter in 
  </code></pre>
  
  <p>I have tried running SQL profiler and found that it appears to be that the error is being thrown during the initial statement preparation, and that the statement preparation SQL appars to be truncating the expression from the field name. So it looks like this:</p>
  
  <pre><code>SET FMTONLY ON select  CASE WHEN [text_result] from table where 1=2 SET FMTONLY OFF go
  </code></pre>
  
  <p>This is confirmed when I run the generated SQL statement through MSSQL management studio and it works fine!</p>
  
  <p>I hope that all makes sense. If anyone has any advice as to if this problem is solvable or if it is just a bug in the driver that would be great!</p>
  
  <p>This is the PHP I am using to run the assembled query (extracted out of a class):</p>
  
  <pre><code>$link = odbc_connect($strConnectionString,$username,$password);
  $rResult = odbc_prepare($link,$qry);
  $success = odbc_execute($rResult,$parameters);
  </code></pre>
  
  <p>var_dump($qry,$parameters):</p>
  
  <pre><code>string 'SELECT * FROM table WHERE 
          CASE WHEN [text_result] IS NOT NULL THEN [text_result] ELSE
              CASE WHEN [last_event] = 1 THEN 'processed' ELSE 'unprocessed' END
          END = ?'
  array (size=1)
    0 =&gt; string 'unprocessed'
  </code></pre>