How to create query based on parameter value?

[psundaravaradhan]

Hi,

 

We are using jaspersoft studio to create a report and i am stuck at query part.

logically the kind of query i want is :

 

  select * from mytable where                    IF (condition1)                              raw_sql_part_1                    ELSE                              raw_sql_part_2[/code]

So now to achive this in "Query editor dialog" i wrote

 

 

  select * from mytable where $P!{param1}[/code]

 

and add "param1" default value in Expression editor as:

 

$P{param2}.equals("A") ?  "1 <> 1" :"1=1" , is 'For prompting' as false[/code]

 

Also added "param2" in parameter list with 'is for prompting' as true

I expect this:

When i hit preview it will prompt me for param2 value and then based on param2 resolve the condition in param1 and then finally substitute that in the actual query.

The default value of the param2 is empty string.

 

What actually happens:

when i hit preview it ask me for the value of param2 which is expected,but value of param2 is not used to resolve condition defined in param1 as param1 condition always resolved to else part i.e "1==1" and the main query also became 

 

select * from mytable where 1==1[/code]

Q1: Am i expecting wrong ?

Q2: Why is param2 is not used to resolve condition defined in param1

 

[zh3ntil]

You should use param1 with exclamation mark and param2 would be defined before param1 on the parameters list. Because param1 should be proccessed after param2 to be generated properly.

SELECT * FROM mytable WHERE $P!{param1}

[psundaravaradhan]

I actually tried the query with exclamation mark as you suggest. Corrected the typo in question. This approach evaluates to default value of param2. And also the order of declaration of parameters is: param2 followed by param1. So, value in param2 should be available to param1 for evaluation when it requires.

 

[zh3ntil]

If your condition always resolved to else part, most probably param2 has null value while param1 is calculating. 

Some possiblities:

  - The order of declaration : If param1 is coming before param2, param2 has null while proccesing the param1. (You said that order is correct but maybe you can check it again)

  - Using different dataset : If you are using different dataset from main dataset, the parameters should be passed to the dataset which will be used. 

[psundaravaradhan]

In my case, param2 has a non-null value, as am giving a default value in <defaultValueExpression> tag of param2. Order is correct. If order is incorrect, i will get compiletime. But am not getting compileerror.

Following happens:

case 1: If I had declared the default value of param2 as 'A', then the first part of condition is true, thus "1<>1" is evaluated, answer is false. so report is empty. This is fine.

case 2: And if I set param2 default value as 'B' then else part "1=1" is evaluated and all data is fetched. This is also correct.

But what am not able to do is : override the default value of param2 at report execution time and force param1 to pickup the current(non-default) value of param2 before evaluating the conditions.

Also, not sure what you mean by using different dataset. Am directly querying a database using a jdbc connection. I can't create a dataset. I can't see the need to do that either.

 

[zh3ntil]

Can you share your jrxml source code?

[psundaravaradhan]

$P{param2}==("A") ?  "1 <> 1" :"1=1"[/code]

It works actually. The mistake I made was, used '==' to compare strings in expression editor instead of '.equals()' . But i was trying too many things at the same time, hence got confused. When i looked into the source code i found I was actually doing  but imagined it was .equals().

Many a thanks for you help !