mark 2010返回#func[感叹号]

Question

我有一个包含开关的sql语句，我想在ms-access-2010上执行.

sql：

SELECT 
TABLE_1.ID, 
SWITCH
(
    (TABLE_3.ID IN (7,8) AND TABLE_1.SOME_COLUMN = 2), (SELECT MAX(TABLE_4.ID) FROM TABLE_4 WHERE  TABLE_4.TABLE_2_ID = TABLE_2.ID),
    (SELECT MIN(TABLE_4.SOME_COLUMN) FROM TABLE_4 WHERE TABLE_4.TBL_2_ID = TABLE_1.TBL_2_ID)
)AS SOME_ALIAS 
FROM 
    ((TABLE_1 
        LEFT JOIN TABLE_2 ON TABLE_1.TABLE_2_ID = TABLE_2.ID)
        LEFT JOIN TABLE_3 ON TABLE_2.TABLE_3_ID = TABLE_3.ID)

在我的结果中，'some_column‘列总是#Function!

如果我不使用switch-语句并将从这两种情况中添加的子选择作为正常选择字段，如下所示：

SELECT 
TABLE_1.ID, 
(SELECT MAX(TABLE_4.ID) FROM TABLE_4 WHERE  TABLE_4.TABLE_2_ID = TABLE_2.ID) AS SOME_COLUMN_1,
(SELECT MIN(TABLE_4.SOME_COLUMN) FROM TABLE_4 WHERE TABLE_4.TBL_2_ID = TABLE_1.TBL_2_ID) AS SOME_COLUMN_2
FROM 
    ((TABLE_1 
        LEFT JOIN TABLE_2 ON TABLE_1.TABLE_2_ID = TABLE_2.ID)
        LEFT JOIN TABLE_3 ON TABLE_2.TABLE_3_ID = TABLE_3.ID)

我没有遇到任何问题(在SOME_COLUMN_1和SOME_COLUMN_2中只有几行没有值)。

是什么导致了#FUNCTION!的结果？

Answer 1

Switch语句需要偶数的参数。您只传递了3个参数，从而导致了一个错误。

您可以使用IIF，也可以添加一个额外的比较参数(如果希望返回最后一个参数(如果第一个参数不是真的话)，可以将True作为第三个参数)。

SELECT 
TABLE_1.ID, 
IIF
(
    (TABLE_3.ID IN (7,8) AND TABLE_1.SOME_COLUMN = 2), (SELECT MAX(TABLE_4.ID) FROM TABLE_4 WHERE  TABLE_4.TABLE_2_ID = TABLE_2.ID),
    (SELECT MIN(TABLE_4.SOME_COLUMN) FROM TABLE_4 WHERE TABLE_4.TBL_2_ID = TABLE_1.TBL_2_ID)
)AS SOME_ALIAS 
FROM 
    ((TABLE_1 
        LEFT JOIN TABLE_2 ON TABLE_1.TABLE_2_ID = TABLE_2.ID)
        LEFT JOIN TABLE_3 ON TABLE_2.TABLE_3_ID = TABLE_3.ID)