不输入参数时忽略where子句中的日期范围参数 [英] Ignore the date range parameter in the where clause when parameter is not entered

问题描述

当没有输入参数时，忽略where子句中的日期范围参数.对于我的日期范围，我使用介于之间.

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

这些参数是从 jasper 报告中输入的

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

推荐答案

您有两种可能来处理可选的输入参数.

You have two possibilities to approach the optional input paramaters.

更简单的方法是使用静态 SQL 并为缺失的参数提供默认值，以便您获得所有匹配项.

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')

更高级的方式由Tom Kyte 并且基于使用动态 SQL.

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

如果提供了参数，则使用BETWEEN谓词生成普通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}

如果参数丢失(即传递了NULL)，您将生成一个不同的SQL，如下所示.

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})

注意，

1) 两个查询变体中绑定变量的数量相同，这很重要，因为您可以使用相同的 setXXXX 语句

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) 由于 shortcut 1 = 1 or 是 between 谓词被忽略，即所有日期都被考虑.

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

应该使用哪个选项?

好吧，对于简单的查询，差别会很小，但是对于具有多个选项缺失参数和大数据的复杂查询，首选动态 SQL 方法.

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.

原因是，使用静态 SQL 时，您可以对更多不同的查询使用相同的语句 - 这里一个用于访问有数据范围，另一个用于访问没有数据范围.

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.

动态选项为每次访问生成不同的 SQL.

The dynamic option produces different SQL for each access.

您可能会在执行计划中看到它:

访问日期范围

-------------------------------------------------------------------------------
| 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)

无数据范围的访问

------------------------------------------------------------------------------
| 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.

这篇关于不输入参数时忽略where子句中的日期范围参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！