Issue with passing null value as parameter value

[nairsunil]

I am facing some issues when we pass in null as value for the parameter in the query.

 

 

Query Executed by Jasper when we pass in the parameter Business Unit

 

select *

from tx_acct_details_merged

where BUSINESS_UNIT = $P{param1}

 

It executes

select *

from tx_acct_details_merged

where BUSINESS_UNIT = null

 

Returns me 0 records

Correct Query

 

select *

from tx_acct_details_merged

where business_unit is null

 

Returns me 10 records

 

How do I dynamically change the query to replace BUSINESS_UNIT = $P{param1} with BUSINESS_UNIT is $P{param1} in case the value for $P{param1} is null?

[acer]

weird problem

 

why dont you set your parameter "= null"

 

it should work...i think

 

check this out http://jasperforge.org/sf/wiki/do/viewPage/projects.jasperreports/wiki/Tutorial_Parameters

 

 

especially this

SELECT * FROM Orders ORDER BY $P!{OrderByClause}

Post edited by: acer, at: 2007/06/14 07:45

[nairsunil]

Passing param = null gives you different results in oracle.

select x , y , z

from table

where x is null

gives different result than

select x , y , z

from table

where x = null

This is the problem i am facing.

 

Any help

[lucianc]

As acer said, you can use the $P!{..} to switch between the two types of where clauses:

Code:

<param name="param1" .../>
<param name="param1Clause" isForPrompting="false">
  <defaultValueExpression>$P{param1} == null ? "IS NULL" : "= $$P{param1}"</defaultValueExpression>
</param>
<queryString>... where BUSINESS_UNIT $P!{param1Clause}</queryString>

 

Note that this would only work in JR 1.3.2 or later. If you use an older version you would need to do instead

Code:

[code] <param name="param1Clause" isForPrompting="false"> <defaultValueExpression>$P{param1} == null ? "IS NULL" : ("= " + $P{param1})</defaultValueExpression> </param>

or, if your param is a String

Code:

[code] <param name="param1Clause" isForPrompting="false"> <defaultValueExpression>$P{param1} == null ? "IS NULL" : ("= '" + $P{param1} + "'"«»)</defaultValueExpression> </param>

 

HTH,

Lucian

Post edited by: lucianc, at: 2007/06/19 05:29