SELECT * FROM mytable WHERE IF (condition1) raw_sql_part_1 ELSE raw_sql_part_2
SELECT * FROM mytable WHERE $P!{param1}
$P{param2}.equals("A") ? "1 <> 1" :"1=1" , IS 'For prompting' AS FALSE
SELECT * FROM mytable WHERE 1==1
6 Answers:
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.
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.
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.
$P{param2}==("A") ? "1 <> 1" :"1=1"
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 !
