How to have conditions in case construct of where clause

Question

I want to have separate conditions according to a value of a column in the table. I'm trying like this but doesnt work.

SELECT  p.flexible_lunch, b.contract 
FROM   SCH360_ACTIVITY_EMP_BASE p, SCH360_BREAKS_EMP b
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.late_start >= p.late_start
        END

Error says, 'missing right parenthesis'

Is this wrong? Cant we have conditions inside case like this? Please correct me.

I think its already answered here https://stackoverflow.com/questions/18104884/conditional-where-clause-with-case-statement-in-oracle — Nitish, Aug 03 '17 at 05:09
no its not a duplicate. In there, no conditions has been changed according to a values in a column. I need to have 2 different conditions according to a specific value in a column. — Nima, Aug 03 '17 at 05:58
I need to set 2 conditions according to the value of a column in the table. case is the solution i think. is there any other way? — Nima, Aug 03 '17 at 08:54

score 1 · Answer 1 · answered Aug 03 '17 at 05:17

1

you can use a simple union to have your two cases.

SELECT  p.flexible_lunch, 
        b.contract 
FROM   SCH360_ACTIVITY_EMP_BASE p
inner join SCH360_BREAKS_EMP b  on      p.resource_qry_seq = b.resource_qry_seq
                                AND     p.resource_id = b.resource_id
                                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
                                        )
                                    )
where b.flexible_time IS NULL

union all 

SELECT  p.flexible_lunch, 
        b.contract 
FROM    SCH360_ACTIVITY_EMP_BASE p
inner join SCH360_BREAKS_EMP b  on      p.resource_qry_seq = b.resource_qry_seq
                                AND     p.resource_id = b.resource_id
                                and     b.late_start >= p.late_start
where   b.flexible_time IS NOT NULL

answered Aug 03 '17 at 05:17

Steven

14,048
6
38
73

This is not what i want. i need to have 2 different conditions according to a specific value in a column. simply like this. If `b.flexible_time IS NULL`, include this condition in the where clause. `SELECT p.flexible_lunch, b.contract FROM SCH360_ACTIVITY_EMP_BASE p, SCH360_BREAKS_EMP b WHERE p.resource_qry_seq = b.resource_qry_seq AND p.resource_id = b.resource_id 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)); ` – Nima Aug 03 '17 at 05:48
else if `b.flexible_time IS NOT NULL` then include this in the condition. `SELECT p.flexible_lunch, b.contract FROM SCH360_ACTIVITY_EMP_BASE p, SCH360_BREAKS_EMP b WHERE p.resource_qry_seq = b.resource_qry_seq AND p.resource_id = b.resource_id AND b.late_start >= p.late_start` – Nima Aug 03 '17 at 05:54
which is exactly how you write it in sql. Please let me know what is wrong with my query despite the fact that you dont like it. – Steven Aug 03 '17 at 06:01
my real question is this https://stackoverflow.com/questions/45479297/how-to-use-case-statement-in-where-condition – Nima Aug 03 '17 at 08:51
dont bother about joining the table, question is about setting 2 conditions according to the value of a column in the table. – Nima Aug 03 '17 at 08:52
that is exactly what the `union` is doing. but apparently you prefer a structure with a `or` which is just about the same. you can accept the answer of abhishek then at least. – Steven Aug 03 '17 at 09:54

score 1 · Answer 2 · answered Aug 03 '17 at 06:30

Instead of using union try this one:

SELECT  p.flexible_lunch ,
        b.contract
FROM    SCH360_ACTIVITY_EMP_BASE p
        INNER JOIN SCH360_BREAKS_EMP b ON p.resource_qry_seq = b.resource_qry_seq
                                          AND p.resource_id = b.resource_id
WHERE   ( ( 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.late_start >= p.late_start )
             AND ( b.flexible_time IS NOT NULL )
           )

not working. this is the problem https://stackoverflow.com/questions/45479297/how-to-use-case-statement-in-where-condition — Nima, Aug 03 '17 at 08:53

score 1 · Answer 3 · answered Aug 03 '17 at 15:01

You can do it without case by playing with the AND/OR conditions. This query will result the same output you want to achieve

SELECT p.flexible_lunch, b.contract
FROM SCH360_ACTIVITY_EMP_BASE p, SCH360_BREAKS_EMP b
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.late_start >= p.late_start)

How to have conditions in case construct of where clause

3 Answers3