Does ODBC support CASE WHEN clause for MS Access? Is there any other database which does not support the CASE WHEN clause? I tried the following query while connecting to MS Access with ODBC but get an exception.
SELECT (CASE WHEN (AGE > 10) THEN 1 ELSE 0 END) FROM demo
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(CASE WHEN (AGE > 10) THEN 1 ELSE 0 END)'
I'm try to find a common way which works for most of the database to generate (compute) the new 'boolean columns' with an comparison expression while connect with ODBC. Actually, MS Access support the comparison in SELECT clause, but for some other databases CASE clause are needed. For MS Access, the SQL can be
SELECT AGE > 10 FROM demo
but in others it have to be
SELECT (CASE WHEN (AGE > 10) THEN 1 ELSE 0 END) FROM demo
You could use IIF statement like in the next example:
SELECT
IIF(test_expression, value_if_true, value_if_false) AS FIELD_NAME
FROM
TABLE_NAME
Since you are using Access to compose the query, you have to stick to Access's version of SQL.
To choose between several different return values, use the switch() function. So to translate and extend your example a bit:
select switch(
age > 40, 4,
age > 25, 3,
age > 20, 2,
age > 10, 1,
true, 0
) from demo
The 'true' case is the default one. If you don't have it and none of the other cases match, the function will return null.
The Office website has documentation on this but their example syntax is VBA and it's also wrong. I've given them feedback on this but you should be fine following the above example.
I have had to use a multiple IIF statement to create a similar result in ACCESS SQL.
IIf([refi type] Like "FHA ST*","F",IIf([refi type]="VA IRRL","V"))
All remaining will stay Null.
Source: Stackoverflow.com