If the statements in the WHERE clause

Question

Is it possible for Oracle to insert conditional IF statements in the WHERE clause?

I want to filter all rows with an end date to today. And if the end date is empty, it should not filter it. I tried this:

SELECT discount_amount FROM vw_ph_discount_data WHERE sysdate > start_date AND IF end_date IS NOT EMPTY THEN sysdate < end_date 

But I get an "invalid relational operator".

Answer 1

You can try:

 SELECT discount_amount FROM vw_ph_discount_data WHERE sysdate > start_date AND sysdate < nvl(end_date,sysdate+1) 

Answer 2

Even if possible, this is not a good idea. Functions in a line can ruin performance.

In this case, the best way is probably just combining two exclusive queries:

 SELECT discount_amount FROM vw_ph_discount_data WHERE sysdate > start_date AND end_date IS NULL UNION ALL SELECT discount_amount FROM vw_ph_discount_data WHERE sysdate > start_date AND end_date IS NOT NULL AND sysdate < end_date 

(changed to NULL from EMPTY , as this seems to be what you were).

Assuming end_date indexed, this should scream, even if it's two queries. The need for additional processing on each returned line is rarely a good idea.

Whatever methods you choose to research, compare them with real-world data. The main optimization directive is a measure, don’t guess.

Answer 3

I do not think that if-else statements can be used in pure Sql code. To achieve your goal, you need to use a stored procedure . I suggest that in your case you can use the following code:

 DECLARE DATE end_date BEGIN IF end_date IS NOT NULL THEN SELECT discount_amount FROM vw_ph_discount_data WHERE sysdate > start_date AND sysdate < end_date; END IF; END; 

Answer 4

Could you do this:

 SELECT discount_amount FROM vw_ph_discount_data WHERE sysdate > start_date AND (end_date IS EMPTY OR sysdate < end_date) 

Answer 5

You can use the IF to create multiple queries or try WHERE (end_date IS NULL OR end_date > SYSDATE) .

Not sure if you should use IS [NOT] EMPTY on "end_date". See ERROR .