Optional parameters in cursor where clause

Question

I have the following sample query which takes values from procedure parameters. The parameter can be either passed or default to null.

SELECT * FROM table
              WHERE( table_term_code = '201931'
              OR (table_term_code = '201931' and table_DETL_CODE ='CA02')
              OR ( table_term_code = '201931' and table_ACTIVITY_DATE = sysdate)
              OR ( table_term_code = '201931' and table_SEQNO = NULL));

i.e the user can input term code and not input any other parameter, or can input term code and table_DETL_CODE and not any other input parameter.

Same goes for the other 2 or conditions.

If a term code is passed and table_DETL_CODE is null, the query should return all the values for that term_code, whereas this query returns null.

Is there a way to achieve this without case or if conditions in PL/SQL?

Oracle has built-in null-handling functions. Refer to the [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Single-Row-Functions.html#GUID-C4201DFA-90C5-46DA-B528-0B6D4E8C647A) — Abra, Jun 01 '20 at 18:12
A Abra - I did try NVL , but my problem is even if the table_DETL_CODE is not null then my query should filter for the second condition instead the where clause gives me all records in term_code. — arsha, Jun 01 '20 at 18:38

score 1 · Answer 1 · answered Jun 01 '20 at 19:38

1

If I understood you correctly, this might be what you're looking for:

select * 
from your_table
where (table_term_code     = :par_term_code     or :par_term_code     is null)
  and (table_detl_code     = :par_detl_code     or :par_detl_code     is null)
  and (table_activity_date = :par_activity_date or :par_activity_date is null)
  and (table_seqno         = :par_seqno         or :par_seqno         is null)

answered Jun 01 '20 at 19:38

Littlefoot

131,892
15
35
57

Thank you . in the above if i pass in value for term_code and do not pass the value for the rest . It does not return records as AND operator looks at the null . If a user passes record only for the first field, my query should return records. The OR operator will return , but my second scenario where i need to filter records for term_code and NOT null detl_code will fail. – arsha Jun 01 '20 at 19:48

score 1 · Answer 2 · answered Jun 03 '20 at 16:06

The description seems to that you require user to enter table_term_code and then either none or exactly 1 of the other 3. If so then perhaps:

select * 
  from your_table
 where table_term_code          = :par_term_code 
   and (   (table_detl_code     = :par_detl_code      and :par_activity_date is null and :par_seqno         is null)
        or (table_activity_date = :par_activity_date  and :par_detl_code     is null and :par_seqno         is null)  
        or (table_seqno         = :par_seqno          and :par_detl_code     is null and :par_activity_date is null)
        or (table_seqno         is null               and :par_detl_code     is null and :par_activity_date is null)
       );

Optional parameters in cursor where clause

2 Answers2