我正在尝试为报告创建此查询,"&BO_STATUS"="AAA"是用户需要输入的值。当值为 AAA 时,我想检索状态为"正在进行"和"已确认"的所有 BO。出于某种原因,我不断收到错误。我一直在尝试以不同的方式做到这一点。
SELECT T1.OPPORTUNITY_NO AS BO_NUMBER,
T1.SOURCE_ID_DB AS SOURCE_ID,
T1.OPPORTUNITY_TYPE,
T1.STATE AS Status
FROM BUSINESS_OPPORTUNITY T1
WHERE
T1.STATE IN
(CASE
WHEN '&BO_Status' = 'AAA' THEN '''In Progress','Confirmed'''
END
)
不要使用case
表达式。 只需使用常规逻辑:
WHERE '&BO_Status' = 'AAA' AND T1.STATE IN ('In Progress', 'Confirmed')
我想你还想要一个"else",所以如果条件不真实,查询会返回一些东西。 如果是这样,那么您需要:
WHERE ('&BO_Status' = 'AAA' AND T1.STATE IN ('In Progress', 'Confirmed')) OR
'&BO_Status' <> 'AAA'
如果明确告诉您使用case..when
语句,则应使用以下语句:
WHERE case when '&BO_Status' = 'AAA'
Then case when T1.STATE IN ('In Progress', 'Confirmed')
Then 1
End
Else 1
End = 1
或者最好的方法是根本不按照@gordon的建议使用case..when
语句。
WHERE ('&BO_Status' = 'AAA' AND T1.STATE IN ('In Progress', 'Confirmed')) OR
'&BO_Status' <> 'AAA'
我正在考虑当状态不AAA
时,您希望每个(没有where
条件(
干杯!!
对于此方案,您可以最大限度地使用INSTR
函数。特别是当您尝试构建字符串条件时。
SELECT T1.OPPORTUNITY_NO AS BO_NUMBER,
T1.SOURCE_ID_DB AS SOURCE_ID,
T1.OPPORTUNITY_TYPE,
T1.STATE AS Status
FROM BUSINESS_OPPORTUNITY T1
WHERE INSTR('In Progress, Confirmed', T1.STATE, 1, 1) > 0