How do we write conditional query in Ireport query executer

[ashcsi1322]

Hello,

Is it possible to write a conditional query in Ireport query executer? For e.g.: something like

IF $P{TEST_PARAM}.equals("test")

select query

ELSE

select query

END IF

Also the value for the parameter should come from the input control.

thanks for the help!

[roger_oe]

can not at least that I know but can you pass a parameter query as armanda already and you can print it directly to the query would be something like

select *
from table1
where $ P {sql}

or wherever you need.

[roger_oe]

can not at least that I know but can you pass a parameter query as armanda already and you can print it directly to the query would be something like

select *
from table1
where $ P {sql}

or wherever you need.

[netti royala]

you should declare parameter in jrxml file and you pass using

$P{},it shows paramter,

$V{},it shows variable

$F{},it shows filed

Example:

Coming to Query:

SELECT * FROM EMPLOYEE WHERE EMP_NAME=$P{Name};

 

 

All d best.

 

[Ankur Gupta]

Hi,

You can do this by simply using the following:

Suppose there is a parameter called $P{TestParamSQL}, the prompt should be turned off as we are setting the value at runtime checking the value of the $P{TEST_PARAM}. 

Set the default value of the parameter as:

For a single condition 

$P{TEST_PARAM}.equals("test")? " select SQL query": " else SQL select query"[/code]

For a multiple condition 

 

$P{TEST_PARAM}.equals("test")? " select SQL query": $P{TEST_PARAM}.equals("test1")? " SQL select query": $P{TEST_PARAM}.equals("test2")?" SQL Query": "else SQL Query"[/code]

 

Now in the iReport Query executor you should give the following expression
$P!{TestParamSQL}
 

Do take care that the dependent parameter $P{TEST_PARAM}. should be set before the $P{TestParamSQL} else would throw a null pointer exception.

Another thing which should be kept in mind is that the query given in the quotes in the expression should be syntactically correct, that is the SQL in as the string should be correct.

Hope that this helps.

 

Thanks,
Ankur Gupta

 

[Ankur Gupta]

Hi, 

You can follow this video, created by me explaning the solution of your question 

Thanks,
Ankur Gupta
http://ankurthetechie.blogspot.in/