Condition in WHERE clause based on PARAMETER

0

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!

anji.viper's picture
Joined: Mar 8 2017 - 2:59am
Last seen: 2 months 1 day ago

6 Answers:

1

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

mlopez_1's picture
909
Joined: Oct 21 2013 - 8:08am
Last seen: 1 week 1 day ago
0

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

mlopez_1's picture
909
Joined: Oct 21 2013 - 8:08am
Last seen: 1 week 1 day ago
0

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.

anji.viper's picture
Joined: Mar 8 2017 - 2:59am
Last seen: 2 months 1 day ago
1

Better use this in where clause:

WHERE $X{IN, id_org, PARAMETER_LIST}

Mariano

mlopez_1's picture
909
Joined: Oct 21 2013 - 8:08am
Last seen: 1 week 1 day ago
0
anji.viper's picture
Joined: Mar 8 2017 - 2:59am
Last seen: 2 months 1 day ago
0

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.

Friendly User's picture
Joined: Oct 8 2009 - 5:59am
Last seen: 4 hours 50 min ago
Feedback