CODEWITHSUNDEEP
sqloracle-databasewhere-clause
Zzz
Zzz

Reputation: 3025

Building a WHERE clause based on whether a field is NULL

I have a function that has a condition in the WHERE clause that compares a field (DATE type) to a date that is passed to the function.

On occasion the date field is NULL. When this occurs I would like to have the comparison to always return true.

What I have currently come up with:

WHERE NVL(table.from_date, TO_DATE('01-JAN-1900')) <= TO_DATE(input_date)

Is there a better way of accomplishing this? For example, building a WHERE clause based on whether table.from_date is NULL. If table.from_date is NULL then the WHERE clause is table.from_date <= TO_DATE(input_date); otherwise the WHERE clause doesn't exist.

Upvotes: 1

Views: 82

Answers (1)

sgeddes
sgeddes

Reputation: 62851

One option is to add a NULL check with OR:

WHERE (table.from_date IS NULL 
      OR table.from_date <= TO_DATE(input_date))

This way, if the from_date is null, it will evaluate to true.

Upvotes: 4

Related Questions