Ignore the date range parameter in the where clause when parameter is not entered

Question

Ignore the date range parameter in the where clause when parameter is not entered. For my date range im using Between.

These parameter are being entered from jasper report

SELECT * 
  from customer 
 where client_id = $P{CLIENT_ID} 
   AND (Account_id = CASE WHEN $P{Account_ID}>0 
                          THEN $P{Account_ID} 
                          ELSE Account_ID END 
        OR Account_ID IS NULL ) 
   AND datetrx BETWEEN $P{DATE_START} AND $P{DATE_END}

if date is not entered the report should bring records of any dates, since date range is not entered

Is the SQL text static or you may generate dynamic SQL based on input parameters? — Marmite Bomber, Jun 26 '19 at 15:20
You could use Oracle's built-in function `nvl`, assuming that an unentered parameter value is the same as a `null`. Are you familiar with that function? If `DATE_START` is null, use another Oracle built-in function `to_date` to create a date so far in the past that all the values of column `datetrx` will be more recent than it. — Abra, Jun 26 '19 at 15:57
thank you for the response @MarmiteBomber yes the sql is dynamic those parameters are entered from a jasper report — nomfundo, Jun 26 '19 at 16:51
thank you for the response @Abra No im not familiar with nvl, just recently started using oracle. in my case the datetrx is never a null value, but i may not enter a parameter and still want to bring everything with anydate. For now when i with the above i get a blank report if i dont enter the date parameter. — nomfundo, Jun 26 '19 at 17:00

Marmite Bomber · Answer 1 · 2019-06-27T05:19:38.213

You have two possibilities to approach the optional input paramaters.

The simpler way is to use static SQL and providing default value for the missing parameters, so that you get all matches.

Here you simple sets the boundaries to the minimum and maximum possible DATE.

select * 
from customer
where customer_id = $P{CLIENT_ID}
and datetrx between nvl($P{DATE_START},date'1900-01-01') 
                and nvl($P{DATE_END},date'2200-01-01')

The more advanced way was popularized by Tom Kyte and is based on using dynamic SQL.

If the paramaters are provided, you generate normal SQL with the BETWEEN predicate:

select * 
from customer
where customer_id = $P{CLIENT_ID}
and datetrx between $P{DATE_START} and $P{DATE_END}

In case the parameter are missing (i.e. NULL is passed) you generate a different SQL as shown below.

select * 
from customer
where customer_id = $P{CLIENT_ID}
and (1=1 or datetrx between $P{DATE_START} and $P{DATE_END})

Note, that

1) the number of the bind variables is the same in both variants of the query, which is important as you can use identical setXXXX statements

2) due to the shortcut 1 = 1 or is the between predicate ignored, i.e. all dates are considered.

Which option should be used?

Well for simple queries there will be small difefrence, but for complex queries with several options of missing parameters and large data, the dynamic SQL approach is preferred.

The reason is, that using static SQL you use the same statement for more different queries - here one for access with data range and one for access without data range.

The dynamic option produces different SQL for each access.

You may see it on the execution plans:

Access with date range

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    22 |     1   (0)| 00:00:01 |
|*  1 |  FILTER           |           |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| CUST_IDX1 |     1 |    22 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(:1)<=TO_DATE(:2))
   2 - access("CUSTOMER_ID"=1 AND "DATETRX">=:1 AND "DATETRX"<=:2)

Access without data range

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    22 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| CUST_IDX1 |     1 |    22 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("CUSTOMER_ID"=1)

Both statements produce different execution plan, that is optimised for the input parameter. In the static option use must share the same execution plan for all input which may cause problems.

Ignore the date range parameter in the where clause when parameter is not entered

1 Answers1

Linked