MS Access支持“CASE WHEN”如果使用ODBC连接？ [英] Does MS Access support "CASE WHEN" clause if connect with ODBC?

问题描述

ODBC是否支持MS Access的 CASE WHEN 子句？是否有任何其他数据库不支持 CASE WHEN 子句？我使用ODBC连接到MS Access时尝试了以下查询，但出现异常。

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驱动程序]语法错误查询表达式'（CASE WHEN（AGE> 10）THEN 1 ELSE 0 END）'

它适用于大多数数据库生成（计算）新的布尔列与连接ODBC的比较表达式。实际上，MS Access支持在SELECT子句中的比较，但对于一些其他数据库CASE子句是必需的。对于MS Access，SQL可以是

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

但在其他情况下必须

SELECT（CASE WHEN（AGE> 10）THEN 1 ELSE 0 END）from demo

推荐答案

由于您使用Access来撰写查询，因此您必须遵守Access的SQL版本。

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

true是默认值。如果没有，并且其他情况都不匹配，则函数将返回null。

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.

Office网站有此文档，但它们的示例语法是VBA，它也是错误的。我已经给他们反馈，但你应该很好，按照上面的例子。

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.

这篇关于MS Access支持“CASE WHEN”如果使用ODBC连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！