SQL 如果使用 ODBC 连接，MS Access 是否支持“CASE WHEN”子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14920116/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does MS Access support "CASE WHEN" clause if connect with ODBC?
提问by Aaron
Does ODBC support CASE WHENclause for MS Access? Is there any other database which does not support the CASE WHENclause? I tried the following query while connecting to MS Access with ODBC but get an exception.
ODBC 是否支持CASE WHENMS Access 子句？是否有任何其他数据库不支持该CASE WHEN子句？我在使用 ODBC 连接到 MS Access 时尝试了以下查询，但出现异常。
SELECT (CASE WHEN (AGE > 10) THEN 1 ELSE 0 END) FROM demo
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)'
[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
我试图找到一种通用方法，该方法适用于大多数数据库，在与 ODBC 连接时使用比较表达式生成（计算）新的“布尔列”。实际上，MS Access 支持在 SELECT 子句中进行比较，但对于其他一些数据库则需要 CASE 子句。对于 MS Access，SQL 可以是
SELECT AGE > 10 FROM demo
SELECT AGE > 10 FROM demo
but in others it have to be
但在其他人中它必须是
SELECT (CASE WHEN (AGE > 10) THEN 1 ELSE 0 END) FROM demo
SELECT (CASE WHEN (AGE > 10) THEN 1 ELSE 0 END) FROM demo
回答by Yawar
Since you are using Access to compose the query, you have to stick to Access's version of SQL.
由于您使用 Access 来编写查询，因此您必须坚持使用 Access 的 SQL 版本。
To choose between several different return values, use the switch() function. So to translate and extend your example a bit:
要在几个不同的返回值之间进行选择，请使用 switch() 函数。因此，稍微翻译和扩展您的示例：
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.
'true' 情况是默认情况。如果您没有它并且其他情况都不匹配，则该函数将返回 null。
The Office websitehas 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.
该办公室网站对本文档，但他们的榜样语法是VBA和它也是错误的。我已经就此向他们提供了反馈，但您应该按照上面的示例进行操作。
回答by Dragoslav
You could use IIFstatement like in the next example:
您可以使用IIF如下示例中的语句：
SELECT
IIF(test_expression, value_if_true, value_if_false) AS FIELD_NAME
FROM
TABLE_NAME
回答by Ron
I have had to use a multiple IIF statement to create a similar result in ACCESS SQL.
我不得不使用多个 IIF 语句在 ACCESS SQL 中创建类似的结果。
IIf([refi type] Like "FHA ST*","F",IIf([refi type]="VA IRRL","V"))
All remaining will stay Null.
所有剩余的将保持 Null。
