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_address FROM t_organisation WHERE id_org = $P{ID_ORGANISATION}
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_address FROM t_organisation WHERE id_org IN ( CASE WHEN $P{ID_ORGANISATION} = '123' THEN ('1231','1232','1233','1234') ELSE $P{ID_ORGANISATION} END )
Any help would be greatly appreciated..
Thanks!
6 Answers:
Yes, you are right.
Then, the only thing i know to do is this:
WHERE id_org IN $P{PARAMETER_LIST}
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})
This should work.
Mariano
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
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_1 FROM .. ) WHERE FIELD_1 ......
Regards,
Mariano
