CODEWITHSUNDEEP
sqloracle-databasecasewhere-clause
Kausty
Kausty

Reputation: 859

SQL - CASE statement in filter with optional use

Sorry I don't have better words to title my questions but essentially I am looking for an implementation of CASE/AND...OR statement in the filter condition where if the value of the particular ID is NOT NULL and greater than 0, then only the condition for that ID would be considered otherwise the condition for that ID need not be used at all.

Example 1:

    select emp_id, cust_id, date_id
    from employee a
    join customer l
    on a.id = l.id
    join time_table ACT
    on a.join_date_id = ACT.date_id
    where a.emp_id = 1
    and l.cust_id in (2, 3)
    and ACT.date_id >= to_char((sysdate - EXTRACT(DAY FROM (SYSDATE))) - 90, 'DD-MON-YYYY')
    and ACT.date_id <= to_char(sysdate -  EXTRACT(DAY FROM (SYSDATE)), 'DD-MON-YYYY')
    and a.sub_emp_id = CASE WHEN @sub_emp_id IS NOT NULL AND @sub_emp_id > 0 THEN @sub_emp_id WHEN @sub_emp_id IS NULL THEN @sub_emp_id ELSE @sub_emp_id END -- where condition is, if sub_emp_id is not null and if sub_emp_id > 0 then use the value of sub_emp_id otherwise if sub_emp_id is NULL, then don't use the condition for sub_emp_id altogether.

Example 2 -

    select emp_id, cust_id, date_id
    from employee a
    join customer l
    on a.id = l.id
    join time_table ACT
    on a.join_date_id = ACT.date_id
    where (a.emp_id = 1
    and l.cust_id in (2, 3)
    and ACT.date_id >= to_char((sysdate - EXTRACT(DAY FROM (SYSDATE))) - 90, 'DD-MON-YYYY')
    and ACT.date_id <= to_char(sysdate -  EXTRACT(DAY FROM (SYSDATE)), 'DD-MON-YYYY')
    and a.sub_emp_id > 0)
    OR
    (a.emp_id = 1
    and l.cust_id in (2, 3)
    and ACT.date_id >= to_char((sysdate - EXTRACT(DAY FROM (SYSDATE))) - 90, 'DD-MON-YYYY')
    and ACT.date_id <= to_char(sysdate -  EXTRACT(DAY FROM (SYSDATE)), 'DD-MON-YYYY')) -- where condition is, "if sub_emp_id is not null and if sub_emp_id > 0" then use the value of sub_emp_id otherwise "if sub_emp_id is NULL, then don't use the condition for sub_emp_id altogether".

Upvotes: 0

Views: 2976

Answers (3)

shawnt00
shawnt00

Reputation: 17915

I think you were shooting for this:

case
    when @sub_emp_id > 0 /* not null is also implied */
    then case when a.sub_emp_id = @sub_emp_id then 1 else 0 end
    else 1
end = 1

And I think these are equivalent alternatives:

not (@sub_emp_id > 0 and a.sub_emp_id <> @sub_emp_id)
coalesce(@sub_emp_id, 0) <= 0 or a.sub_emp_id = @sub_emp_id

Upvotes: 2

Hogan
Hogan

Reputation: 70523

Often you need to to have null match to "all". The easy pattern for that is this:

WHERE coalesce(@sub_emp_id, a.sub_temp_id) = a.sub_temp_id

Upvotes: 1

sstan
sstan

Reputation: 36493

If I understand you correctly, you'll want your filter on sub_emp_id to look like this:

...
and (@sub_emp_id is null or
     @sub_emp_id <= 0 or
     a.sub_temp_id = @sub_emp_id)

Upvotes: 1

Related Questions