Condition in WHERE clause based on PARAMETER

[anji.viper]

Hello guys,

I would like to know whether it is possible to use a CASE condition in my WHERE clause using a parameter.

For example, I have a query which takes the ID of the organisation as parameter:

SELECT org_name, org_addressFROM t_organisationWHERE id_org = $P{ID_ORGANISATION}[/code]

But it turns out that I have some organisations which have multiple rows in the table t_organisation and I want to use all their IDs in the query using the parameter..

What I want is something like this:

SELECT org_name, org_addressFROM t_organisationWHERE id_org in (    CASE when $P{ID_ORGANISATION} = '123'     then ('1231','1232','1233','1234')     else $P{ID_ORGANISATION}     end)[/code]

Any help would be greatly appreciated..

Thanks!

[mlopez_1]

I think this is an SQL question.

Try putting:

CASE WHEN $P{ID_ORGANISATION} = '123'     THEN ('1231','1232','1233','1234')     ELSE $P{ID_ORGANISATION}END[/code]

As a field and wrap this select with other select you can use for where clause for this field

SELECT *FROM(SELECT ..       CASE WHEN $P{ID_ORGANISATION} = '123'            THEN ('1231','1232','1233','1234')            ELSE $P{ID_ORGANISATION}       END AS FIELD_1FROM ..)WHERE FIELD_1 ......[/code]

Regards,

Mariano

[anji.viper]

Hi Mariano, thanks for the answer.. But I don't quite understand how to use it in the WHERE clause..

CASE statement does not let us return more than 1 value, which is what's causing the SQL error. This is what I want to bypass.

[mlopez_1]

Yes, you are right.

Then, the only thing i know to do is this:

WHERE id_org IN $P{PARAMETER_LIST}[/code]

And use PARAMETER_LIST as java.util.List and its Default Value Expression as:

$P{ID_ORGANISATION}.equals("123") ?    Arrays.asList("1231","1232","1233","1234") :    Arrays.asList($P{ID_ORGANISATION})[/code]

This should work.

Mariano

[mlopez_1]

Better use this in where clause:

WHERE $X{IN, id_org, PARAMETER_LIST}[/code]

Mariano

[Friendly User]

You can also try using the $P!{} syntax which first evaluates the parameter value and then puts whatever is in the parameter value directly into the SQL text and then executes the resulting SQL. You can read more about $P!{} in the JasperReports Ultimate Guide.