PL/SQL Optional parameters in where

Question

I would really appreciate the help with this consultation

I would like to know how to set optional parameters in the where

WHERE T.ID_NUM = :ID_NUM
OR ASOL.CASE_ID = :CASE_ID

for example:

ID_NUM cannot have any value in the parameter, but CASE_ID does have a value in the parameter
ID_NUM has a value in the parameter, but CASE_ID has no value in the parameter
ID_NUM has value in parameter and CASE_ID has value in parameter

when I say it has no value the parameter is empty or "undefined"

Thank you

score 2 · Accepted Answer · answered Jul 17 '20 at 01:24

2

I think you want:

WHERE (T.ID_NUM = :ID_NUM OR :ID_NUM IS NULL) AND
      (ASOL.CASE_ID = :CASE_ID OR :CASE_ID IS NULL)

I am guessing you actually want AND, not OR between the conditions.

answered Jul 17 '20 at 01:24

Gordon Linoff

1,242,037
58
646
786

1

This also results in true if both are null. Is that valid as per the requirement? – Vijay Jul 17 '20 at 07:11
@Vijay makes a good point here: if both are NULL the query would return the entire table which is probably not desirable. You most likely want to add AND (:ID_NUM IS NOT NULL OR :CASE_ID IS NOT NULL). – Kevin Seymour Jul 31 '20 at 19:07

score 1 · Answer 2 · answered Jul 22 '20 at 00:59

You can add a NVL function in your clause, if you send a null value in the parameters, then the query are going to use the second comparisson and the second comparisson always be true

WHERE  T.ID_NUM     = NVL(:ID_NUM  , T.ID_NUM    )
  OR   ASOL.CASE_ID = NVL(:CASE_ID , ASOL.CASE_ID)

PL/SQL Optional parameters in where

2 Answers2

Related