17

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

Aaron
  • 573
  • 1
  • 5
  • 15
  • I seem to recall that you had use `IIF` in MS Access instead of `CASE` (that's not specific to ODBC, just the general Access thing). – Anton Kovalenko Feb 17 '13 at 10:30
  • Yes, but `IIF` is MS Access specific. I want to make sure whether ODBC support `CASE` for MS Access. – Aaron Feb 17 '13 at 10:34
  • 3
    When sending statements using ODBC you must use SQL that is supported by the target database. MS Access doesn't support CASE statements, so ODBC connections to MS Access cannot "support" them either. – Lord Peter Feb 17 '13 at 11:04
  • 1
    @LordPeter I doubt if the OP will get a better answer. ( How is Harriet Vane these days? ) – Fionnuala Feb 17 '13 at 11:14
  • @Remou My origins are humble, but they are charming stories :) – Lord Peter Feb 17 '13 at 14:45

3 Answers3

34

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.

Yawar
  • 11,272
  • 4
  • 48
  • 80
  • 1
    `Switch()` returns the value assigned to the first condition which matches. So if age is 40, that expression will return 1. Order the conditions by descending age instead. But we don't even know whether Aaron wants to evaluate more than one condition. – HansUp Feb 17 '13 at 17:05
  • @HansUp - thank you for catching that! *slapping own forehead* And yes, we don't know if he wants multiple cases, but I just wanted to show a general form of the syntax and he can delete the extra cases he doesn't need. – Yawar Feb 17 '13 at 19:06
  • @Yawar @HansUp Thanks guys, 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. Actually, MS Access support the comparison in `SELECT` clause, but for some other databases `CASE` clause are needed. In 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` – Aaron Feb 18 '13 at 01:41
  • @yawar +1 for the SWITCH. Since you seem to have research on the docs, could you throw some light into a performance comparison between switch and IIF? From whst I encounter IIF slow... – bonCodigo Aug 13 '13 at 02:04
  • 1
    @bonCodigo I don't know much about performance characteristics _but_ in my experience `switch()` is bad at handling comparisons to `null`. So if you're doing something like `switch(something is null, 1, ...)` you should instead use `iif(something is null, 1, ...)`. – Yawar Aug 16 '13 at 04:43
16

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
shA.t
  • 16,580
  • 5
  • 54
  • 111
Dragoslav
  • 176
  • 1
  • 4
1

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.

Petter Friberg
  • 21,252
  • 9
  • 60
  • 109
Ron
  • 11
  • 1