我试图在MS Access中使用SWITCH函数,但我一直被告知参数的数量不正确。我不熟悉switch函数,但我理解语法。
开关(expression1,value1,expression2,value2,…expression\u n,value\u n)
我是否在交换机中做了一些无法使用的事情,子查询可能?我已经检查了逗号、括号,以及是否有一个表达式和一个返回值。当我从T-SQL转换到jet并替换一个运行良好的CASE语句时,这让我抓狂。
Switch(
(eh.STARTDTE <> sh.STARTDTE AND IsNull(eh.ENDDTE,"") <> IsNull(sh.ENDDTE,"") AND eh.STARTDTE < sh.STARTDTE),
"FAIL - Employment Start Date Before Service Start Date",
(eh.STARTDTE <> sh.STARTDTE AND IsNull(eh.ENDDTE,"") <> IsNull(sh.ENDDTE,"") AND eh.ENDDTE > sh.ENDDTE),
"FAIL - Employment End Date After Service End Date",
(eh.STARTDTE <> sh.STARTDTE AND IsNull(eh.ENDDTE,"") <> IsNull(sh.ENDDTE,"") AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNO
AND eh2.EMPID = sh.EMPID
AND eh2.ENDDTE = DateAdd(dd,-1,eh.STARTDTE)
) = 0),
"FAIL - Previous Employment Period Corrupt or Missing",
(eh.STARTDTE <> sh.STARTDTE AND IsNull(eh.ENDDTE,"") <> IsNull(sh.ENDDTE,"") AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNo
AND eh2.EMPID = sh.EMPID
AND eh2.ENDDTE = DateAdd(dd,-1,eh.STARTDTE)
) = 0),
"FAIL - Previous Employment Period Corrupt or Missing",
(eh.STARTDTE <> sh.STARTDTE AND IsNull(eh.ENDDTE,"") <> IsNull(sh.ENDDTE,"") AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNO
AND eh2.EMPID = sh.EMPID
AND eh2.STARTDTE = DateAdd(dd,1,eh.ENDDTE)
) = 0),
"FAIL - Next Employment Period Corrupt or Missing",
(eh.STARTDTE = sh.STARTDTE AND eh.ENDDTE <> sh.ENDDTE AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNO
AND eh2.EMPID = sh.EMPID
AND eh2.STARTDTE = DateAdd(dd,1,eh.ENDDTE)
) = 0),
"FAIL - Next Employment Period Corrupt or Missing",
(eh.STARTDTE <> sh.STARTDTE AND eh.ENDDTE = sh.ENDDTE AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNO
AND eh2.EMPID = sh.EMPID
AND eh2.ENDDTE = DateAdd(dd,-1,eh.STARTDTE)
) = 0),
"FAIL - Previous Employment Period Corrupt or Missing",
(eh.STARTDTE = sh.STARTDTE AND eh.ENDDTE = sh.ENDDTE AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNO
AND eh2.EMPID = sh.EMPID
) <> 1),
"FAIL - Too Many or No Employment History Records"
) AS "Reason"
更新了答案
Switch(
(eh.STARTDTE <> sh.STARTDTE AND NZ(eh.ENDDTE,"") <> NZ(sh.ENDDTE,"") AND eh.STARTDTE < sh.STARTDTE),
"FAIL - Employment Start Date Before Service Start Date",
(eh.STARTDTE <> sh.STARTDTE AND NZ(eh.ENDDTE,"") <> NZ(sh.ENDDTE,"") AND eh.ENDDTE > sh.ENDDTE),
"FAIL - Employment End Date After Service End Date",
(eh.STARTDTE <> sh.STARTDTE AND NZ(eh.ENDDTE,"") <> NZ(sh.ENDDTE,"") AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNO
AND eh2.EMPID = sh.EMPID
AND eh2.ENDDTE = DateAdd("dd",-1,eh.STARTDTE)
) = 0),
"FAIL - Previous Employment Period Corrupt or Missing",
(eh.STARTDTE <> sh.STARTDTE AND NZ(eh.ENDDTE,"") <> NZ(sh.ENDDTE,"") AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNo
AND eh2.EMPID = sh.EMPID
AND eh2.ENDDTE = DateAdd("dd",-1,eh.STARTDTE)
) = 0),
"FAIL - Previous Employment Period Corrupt or Missing",
(eh.STARTDTE <> sh.STARTDTE AND NZ(eh.ENDDTE,"") <> NZ(sh.ENDDTE,"") AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNO
AND eh2.EMPID = sh.EMPID
AND eh2.STARTDTE = DateAdd("dd",1,eh.ENDDTE)
) = 0),
"FAIL - Next Employment Period Corrupt or Missing",
(eh.STARTDTE = sh.STARTDTE AND eh.ENDDTE <> sh.ENDDTE AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNO
AND eh2.EMPID = sh.EMPID
AND eh2.STARTDTE = DateAdd("dd",1,eh.ENDDTE)
) = 0),
"FAIL - Next Employment Period Corrupt or Missing",
(eh.STARTDTE <> sh.STARTDTE AND eh.ENDDTE = sh.ENDDTE AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNO
AND eh2.EMPID = sh.EMPID
AND eh2.ENDDTE = DateAdd("dd",-1,eh.STARTDTE)
) = 0),
"FAIL - Previous Employment Period Corrupt or Missing",
(eh.STARTDTE = sh.STARTDTE AND eh.ENDDTE = sh.ENDDTE AND (
SELECT
COUNT(*)
FROM
emphist AS eh2
WHERE
eh2.MEMBNO = sh.MEMBNO
AND eh2.EMPID = sh.EMPID
) <> 1),
"FAIL - Too Many or No Employment History Records"
) AS "Reason"
