How to use optional parameters

[javaiiispl]

 Hi i am using IReport/Jasper Reports and the query for my situation is

 

SELECT *  FROM admin WHERE age=$P{age} and tnumber=$P{tno}

 

Now i am getting input for both fields ie age and tnumber from user.

In this case three possible condition occurs

cond1:- user may enter both age and tumber fields

cond2:-  user may enter only age

cond3:- user may enter only tnumber

 

Above query is executed only if user enters both values, if user enters value for a single field it shows document has no pages.

 

i want to satisfy all my condition(cond1,2,3) in a single query, Please help me by suggesting necessary query for above condition..

 

Thanks in advance..

 

[gail26]

I am new to iReport too and there might be better solution.

 

Create another parameter that will construct your where clause.

On this parameter it will check both parameter if they are null or not using conditional statement.

[javaiiispl]

 I got solution for above issue.

Just pass the query as

SELECT *  FROM admin WHERE ((age=$P{age} or $P{age} is null)  and (tnumber=$P{tno} or $P{tno} is null))

This will show necessary output.

[jpauze]

Glad you found a solution,   Gail was on the right path as well and probably what I would have done. 

There are a ton of posts on the forum with how to use Parameters in SQL statements, both for doing parameter substitution as well as dynamically building SQL strings.   Just do a search for Parameter and you can usually find what you are looking for. 

Here is a similar one I responded to recently and I know Matt has a number of posts on this topic.

http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=86329