Automatically discard conditions with empty params

[edigest]

Hi,

I'm a newbie and I would like to know if there is a way to discard WHERE conditions when their parameters are null or empty,

Example:

SELECT * FROM table1 WHERE (FIELD1=$P!{PARAM1}) AND (FIELD2=$P!{PARAM2})

should be executed as

SELECT * FROM table1 WHERE (FIELD1=$P!{PARAM1})

if $P!{PARAM2) is null or empty (empty string for strings, 0 for numbers)

In another RAD tool that we are using this feature is called "REMOVE ON EMPTY PARAMETER"

 

I know I can "embed" this behaviour directly in the query, but it becomes a pain if the query is rather complex like:

SELECT * FROM table1 WHERE

( FIELD1=$P!{PARAM1} OR FIELD2=$P!{PARAM2} ) AND

( FIELD5<>$P!{PARAM2} OR FIELD6>$P!{PARAM3} OR FIELD7=$P!{PARAM4} )

 

The feature is VERY useful if we use the same query in many parametrized contexts (it avoids building and mantaining many smaller queries)

Thank you in advance,

  M.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[Giulio Toffoli]

There is not this such of function. Since the query is arbitrary, JasperReports would not be able to know how to remove a particular condition.

There are at least 2 workarounds, but both involves in some effort. The first is to check if your param is null in the query:

( $P{PARAM1} is null OR FIELD1=$P{PARAM1})

The second is to move the where condition in a parameter (it must be the LAST parameter in the list), with a default value expression like:

"WHERE 1=1 "  +
($P{PARAM1} != null)  ?  "AND (FIELD1 = " + $P{PARAM1} : ")" +
($P{PARAM2} != null)  ?  "AND (FIELD2 = " + $P{PARAM2} : ")"

The query will be something like:

SELECT * FROM table1 $P!{MyConditionParameter}

A final note about the syntax to use a parameter in a query string:
$P!{ }  this syntax is used to replace the parameter placeholder with its value as raw chunk of query
$P{ }  this syntax is used to treat the parameter as SQL parameter, JasperReports will prepare a statement (replacing the placeholder with a question mark '?') and will pass the parameters correctly later.

Giulio