Oracle Case in WHERE子句，包含多个条件

Question

我试图在我的where子句中传递一个case语句，但似乎我错过了一些东西，或者在我的场景中不可能，下面是我的代码

代码解释"如果日期是星期天，我想检索过去3天的数据"否则取昨天的数据">

FROM lea_nf1.lea_agreement_dtl c--, lea_nf1.LEA_GUARANTOR_HIRER_DTL d
WHERE
case when to_char(sysdate,'Day') = 'Sunday' then 
to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm')
and to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy')
and C.DISBURSALDATE between trunc(sysdate-3) and trunc(sysdate-1)
else 
to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm')
and (to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy')
and C.DISBURSALDATE < sysdate end
ORDER BY C.DISBURSALDATE desc

下面是错误

ORA-00905:缺少关键字

Answer 1

正如您所看到的，您不能使用case语句来构建这样的where子句。但是，您可以使用逻辑运算符来获得所需的行为:

WHERE
(to_char(sysdate,'Day') = 'Sunday' AND 
to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm') AND
to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy') AND
C.DISBURSALDATE between trunc(sysdate-3) and trunc(sysdate-1)) OR
(to_char(sysdate,'Day') != 'Sunday' AND
to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm') AND
to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy') AND
C.DISBURSALDATE < sysdate)

这当然可以通过提取or算子两边的公共条件来简化:

WHERE
to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm') AND
to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy') AND
(to_char(sysdate,'Day') = 'Sunday' AND 
C.DISBURSALDATE between trunc(sysdate-3) and trunc(sysdate-1)) OR
(to_char(sysdate,'Day') != 'Sunday'  AND
C.DISBURSALDATE < sysdate)

或者，如果你更进一步，任何between trunc(sysdate-3) and trunc(sysdate-1)的C.DISBURSALDATE也总是< sysdate，所以:

WHERE
to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm') AND
to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy') AND
C.DISBURSALDATE < sysdate AND
(to_char(sysdate,'Day') != 'Sunday' OR
C.DISBURSALDATE between trunc(sysdate-3) and trunc(sysdate-1))

Answer 2

CASE语句返回一个"列值";不能作为WHERE CONDITION本身求值，但可以根据sysdate将其用作值1或3，然后在筛选条件中使用此值:

WHERE
TRUNC(C.DISBURSALDATE) >=
TRUNC(
SYSDATE  - 
(CASE WHEN to_char(sysdate,'Day') = 'Sunday' THEN 3 ELSE 1 END) -- this case will return 3 or 1, and use it to subtract from sysdate
)
AND TRUNC(C.DISBURSALDATE) < TRUNC(SYSDATE) --this is just because I am not sure if your data has dates after today

同样，您不需要下面的子句来过滤日期:

to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm') AND
to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy')