Optional Parameters in SQL

[neilmusgrove]

Hi i am using IReport/Jasper Reports and I have mastered inserting parameters into the SQL. Can anyone tell me if it is possible to insert optional parameters into the SQL that a user may or may not select

EG

 

SELECT * FROM CAT {WHERE USERSELECTION = 1}

The problem with the only way i know at the moment is you would be left with the where clause even if the user did not use the parameter, i would expect you to be able to wrap the whole where clause in the property statement as above.

Thanks

 

Neil Musgrove

[neilmusgrove]

Thanks I have discovered the $P!{} notation which has allowed me to achieve this.

[rajeshsekar]

please send the query for optional parameter  $p!{}. i dont know how to use that 

select * from admin {age=$p!{age}}......is that correct ....its not working anymore 

 

Thanks in advance 

[javaiiispl]

 Me too have same problem.

I dont know how to use optional parameter, please help me..

 

Thanks in advance..

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

 

[jpauze]

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

 

[subodhjoshi82]

Can you please post your whole query my Jasper report not working.I Did like this...Select columnA,ColumnB From Table Where Table."columnA" = $P!{}