CASE in MSSQL WHERE clause - odbc error

Question

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.

So, for example, I have a derived field such as the following:

CASE WHEN [text_result] IS NOT NULL THEN [text_result] ELSE
    CASE WHEN [last_event] = 1 THEN 'processed' ELSE 'unprocessed' END
END

If I try and filter this using the value "unprocessed" then the final assembled query is obviously something akin to the following:

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'

However when this runs I receive the following error:

Warning: odbc_execute(): SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'from'., SQL state 37000 in SQLDescribeParameter in 

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:

SET FMTONLY ON select  CASE WHEN [text_result] from table where 1=2 SET FMTONLY OFF go

This is confirmed when I run the generated SQL statement through MSSQL management studio and it works fine!

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!

This is the PHP I am using to run the assembled query (extracted out of a class):

$link = odbc_connect($strConnectionString,$username,$password);
$rResult = odbc_prepare($link,$qry);
$success = odbc_execute($rResult,$parameters);

var_dump($qry,$parameters):

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 => string 'unprocessed'

Answer 1

First, you don't need nested case statements. You can just do:

CASE WHEN [text_result] IS NOT NULL THEN [text_result] 
     WHEN [last_event] = 1 THEN 'processed' 
     ELSE 'unprocessed'
END

One thing that I see wrong with your select is the =. You can write:

SELECT unprocessed = (CASE WHEN [text_result] IS NOT NULL THEN [text_result] 
                           WHEN [last_event] = 1 THEN 'processed' 
                           ELSE 'unprocessed'
                      END)

or

SELECT (CASE WHEN [text_result] IS NOT NULL THEN [text_result] 
             WHEN [last_event] = 1 THEN 'processed' 
             ELSE 'unprocessed'
        END) as unprocessed

But, when using =, the variable comes first.

Then, I don't think you can specify a column alias using ?. You have to construct the query string with the column alias name in it.