NULL handling enhancement in PL/SQL query

The below query doesn’t list out all records that contain null value in WFD_DETECTION_EPA when we pass in some value in S1_WFD_OPERATION_CODE variable. Only records with WFD_DETECTION_EPA is not null appear in the result.

How to enhance this query?

           AND UPPER (FD.WFD_DETECTION_EPA) LIKE
                      '%'
                   || CASE
                         WHEN LENGTH (S0_WFD_DETECTION_EPA) > 0
                         THEN
                            UPPER (S0_WFD_DETECTION_EPA)
                         ELSE
                            '%'
                      END
                   || '%'
            AND UPPER (FD.WFD_OPERATION_CODE) LIKE
                      '%'
                   || CASE
                         WHEN LENGTH (S1_WFD_OPERATION_CODE) > 0
                         THEN
                            UPPER (S1_WFD_OPERATION_CODE)
                         ELSE
                            '%'
                      END
                   || '%'

Answer

You can use condition combined with OR

AND (UPPER (FD.WFD_DETECTION_EPA) LIKE
                      '%'
                   || CASE
                         WHEN LENGTH (S0_WFD_DETECTION_EPA) > 0
                         THEN
                            UPPER (S0_WFD_DETECTION_EPA)
                         ELSE
                            '%'
                      END
                   || '%' OR FD.WFD_DETECTION_EPA IS NULL) -- this
            AND UPPER (FD.WFD_OPERATION_CODE) LIKE
                      '%'
                   || CASE
                         WHEN LENGTH (S1_WFD_OPERATION_CODE) > 0
                         THEN
                            UPPER (S1_WFD_OPERATION_CODE)
                         ELSE
                            '%'
                      END
                   || '%'

Cheers!!