在何处条件下如何使用案例陈述? [英] How to use case statement in where condition?

问题描述

我需要在WHERE子句中使用CASE语句，例如:

I need to use the CASE statement in the WHERE clause like:

WHERE  p.resource_qry_seq = b.resource_qry_seq
AND    p.resource_id = b.resource_id
AND    (CASE
         WHEN b.flexible_time IS NULL THEN
          ((b.activity_start >= p.activity_start AND b.activity_end < p.activity_end) OR
          (b.activity_start > p.activity_start  AND b.activity_end <= p.activity_end))
         ELSE
               b.activity_start > p.late_start
         END)

我正在缺少右括号"如何根据列中的值包含此类条件?

I'm getting, 'missing right parenthesis' How to include such conditions according to a value in a column?

推荐答案

CASE表达式的谓词(即THEN之后的谓词)必须是一个值，而不是逻辑.您可以重新定义WHERE子句，如下所示:

The predicate of a CASE expression (i.e. what comes after THEN) has to be a value, rather than logic. You can rephrase your WHERE clause as follows:

WHERE
    p.resource_qry_seq = b.resource_qry_seq AND
    p.resource_id = b.resource_id AND
    ((b.flexible_time IS NULL AND
        (b.activity_start >= p.activity_start AND b.activity_end < p.activity_end) OR
        (b.activity_start > p.activity_start  AND b.activity_end <= p.activity_end)) OR
    (b.flexible_time IS NOT NULL AND b.activity_start > p.late_start))

这篇关于在何处条件下如何使用案例陈述?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！