Conditionned where lines

[jadey]

Hello people;
I have reports where requests ("where") must be dynamic.
I would write something like:

select t.champ
from table t
where t.TRADEDATE <= case when $P{datedeb} is null then a.TRADEDATE else $P{datedeb} end

but it doesn't work while

select t.champ
from table t
where t.TRADEDATE <= case when $P{datedeb} ='01/01/3000' then a.TRADEDATE else $P{datedeb} end

works fine.

In short, if a parameter is the an empty string or null then not integrate them as conditions on the "where"
I saw that it is permitted to use an object of type "JRDesignQuery" to build the request in java. Two pitfalls for this method:
1 - Forced to recompile each time the report (I've been using the compiled .jasper reports)
2 - The real pitfalls: I have a lot of reports and the requests are enormous. It would quickly become unmanageable if report request are in java side.
There's also another possibility to pass the request as a parameter to the report. This solution has the same constraints, and is similar in all points to the first.

If someone has a solution to propose to me I shall be grateful to him.
Thank you for all your contributions.

[lucianc]

You can use $P!{..} and a derived parameter for that.  See below.

HTH,

Lucian

 

Code:

<parameter name="QueryFilter" isForPrompting="false">  <defaultValueExpression>$P{datedeb} == null || $P{datedeb}.equals("") ? "" : "when $$P{datedeb} ..."</defaultValueExpression></parameter><queryString>select ... where P$!{QueryFilter}</queryString>