title: SQL - CASE statement in filter with optional use
body: <p>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.</p>
  
  <p>Example 1:</p>
  
  <pre><code>    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 &gt;= to_char((sysdate - EXTRACT(DAY FROM (SYSDATE))) - 90, 'DD-MON-YYYY')
      and ACT.date_id &lt;= 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 &gt; 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 &gt; 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.
  </code></pre>
  
  <p>Example 2 -</p>
  
  <pre><code>    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 &gt;= to_char((sysdate - EXTRACT(DAY FROM (SYSDATE))) - 90, 'DD-MON-YYYY')
      and ACT.date_id &lt;= to_char(sysdate -  EXTRACT(DAY FROM (SYSDATE)), 'DD-MON-YYYY')
      and a.sub_emp_id &gt; 0)
      OR
      (a.emp_id = 1
      and l.cust_id in (2, 3)
      and ACT.date_id &gt;= to_char((sysdate - EXTRACT(DAY FROM (SYSDATE))) - 90, 'DD-MON-YYYY')
      and ACT.date_id &lt;= 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 &gt; 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".
  </code></pre>