我在位于 where 子句中的最后一个 CASE 语句上收到编译器错误。我似乎无法弄清楚我做错了什么...任何帮助将不胜感激,谢谢。
P1: BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT DISTINCT MPWBIPS.MIP_NO,
MPWBIPS.ITEM_NO,
MPWBIPS.MATL_PLNR_ID,
MPWBIPS.ITEM_OBS_STATUS,
MPWBMIT.EXCS_ITEM_COST_TOT,
MPWBMIP.TRGT_CHGOVR_DATE,
SLOTLOC.LOT_FROM_EFF_DATE,
FROM MPWBIPS MPWBIPS
INNER JOIN MPWBMIT MPWBMIT
ON MPWBMIT.MIP_NO = MPWBIPS.MIP_NO
AND MPWBMIT.ITEM_NO = MPWBIPS.ITEM_NO
INNER JOIN MPPLNR MPPLNR
ON MPWBIPS.MATL_PLNR_ID = MPPLNR.MATL_PLNR_ID
INNER JOIN MPWBMIP MPWBMIP
ON MPWBIPS.MIP_NO = MPWBMIP.MIP_NO
INNER JOIN SMPROJ_DPIMS SMPROJ
ON MPWBIPS.MIP_NO = SMPROJ.MIP_NO
INNER JOIN SLOTLOC_DPIMS SLOTLOC
ON SMPROJ.MFG_CHGOVR_PER = SLOTLOC.LOT
WHERE plannerID =
CASE WHEN mgrIndc = 'Y' THEN
MPPLNR.MGR_LID
WHEN suprvIndc = 'Y' THEN
MPPLNR.SPRVSR_LID
WHEN plannerID = '' THEN
''
ELSE
MPPLNR.MATL_PLNR_ID
END
AND CASE WHEN obsStatus = 'ACTION' THEN
MPWBIPS.ITEM_OBS_STATUS <> 'RESOLVED 99'
AND MPWBIPS.ITEM_OBS_STATUS <> 'EXCESS 90'
WHEN obsStatus = 'ALL' OR obsStatus = '' THEN
obsStatus = ''
ELSE
MPWBIPS.ITEM_OBS_STATUS = obsStatus
END
AND (COALESCE(mipNo, '') = '' OR MPWBIPS.MIP_NO = mipNo);
OPEN cursor1;
尾页 P1
不要认为你可以在这种方法中使用 if。 但是,您可以嵌套案例。
case when parm1 <> '' THEN
CASE parm1
WHEN '1' THEN
--do something
WHEN '2' THEN
--do something
ELSE
--do something
END
end
但是为什么? 相反,只需添加到您的案例中
CASE parm1
WHEN '1' THEN
--do something
WHEN '2' THEN
--do something
WHEN <> ''
--do something
END
不能
case
语句嵌套if
语句中。考虑重写查询。
你最后错过了case
。您的案例陈述应该是
CASE WHEN obsStatus = 'ACTION' THEN
MPWBIPS.ITEM_OBS_STATUS <> 'RESOLVED 99'
AND MPWBIPS.ITEM_OBS_STATUS <> 'EXCESS 90'
WHEN obsStatus = 'ALL' OR obsStatus = '' THEN
obsStatus = ''
ELSE
MPWBIPS.ITEM_OBS_STATUS = obsStatus
END CASE
编辑:
<>
(不相等)运算符正在制造问题;你不能在那里使用它们。不确定为什么您使用 在大小写条件下不相等。以下两行
MPWBIPS.ITEM_OBS_STATUS <> 'RESOLVED 99'
AND MPWBIPS.ITEM_OBS_STATUS <> 'EXCESS 90'
可能会使您的 Where 条件如下所示:
WHERE plannerID =
(CASE WHEN mgrIndc = 'Y' THEN
MPPLNR.MGR_LID
WHEN suprvIndc = 'Y' THEN
MPPLNR.SPRVSR_LID
WHEN plannerID = '' THEN ''
ELSE MPPLNR.MATL_PLNR_ID
END)
AND
((obsStatus = 'ACTION') and (MPWBIPS.ITEM_OBS_STATUS <> 'RESOLVED 99'
AND MPWBIPS.ITEM_OBS_STATUS <> 'EXCESS 90'))
or
((obsStatus = 'ALL' OR obsStatus = '') and (obsStatus = ''))
or
MPWBIPS.ITEM_OBS_STATUS = obsStatus
AND
(COALESCE(mipNo, '') = '' OR MPWBIPS.MIP_NO = mipNo);