CODEWITHSUNDEEP
sqljasper-reports
Bilal Dekar
Bilal Dekar

Reputation: 3986

How can I add a where statement on list conditionally in report query?

How can I add a where statement in iReport query with a condition that a list is not empty:

WHERE
CASE WHEN length($P{list}) > 0 THEN table_x.id IN ($P!{list}) END

I tried to display the report passing an empty list, but it does not work.

Upvotes: 2

Views: 2381

Answers (3)

Petter Friberg
Petter Friberg

Reputation: 21710

Jasper report has it's own query system and I presume your $P{list} is a class that extends java.util.Collection for example a java.util.List, since you state "passing an empty list"

To create a prepared statement IN query on Collection in jasper report you use:

WHERE $X{IN, table_x.id, list}

Do note that if list is null or it is an empty list this query will return all records, see using-parameters-queries

JasperReports handles special characters in each value. If the parameter is null or contains an empty list, meaning no value has been set for the parameter, the entire $X{} clause is evaluated as the always true statement “0 = 0”.

To not show any records, you need to add at least one null value to the List in java before you pass parameter to jasper-report.

if (list.isEmpty()){
    list.add(null);
}

Please also note that you should avoid using $P!{param} since this creates an sql through string concatenation an open your application to SQL injection attacks, always try to use prepared statement instead.

Upvotes: 2

PeterH
PeterH

Reputation: 1040

If i understood correctly, try using this:

WHERE (CASE WHEN length($P{list}) > 0 THEN table_x.id IN ($P!{list}) END) <> ''

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270523

Your ELSE clause is NULL, and that is false. I would recommend avoiding the CASE:

WHERE length($P{list}) = 0 OR table_x.id IN ($P!{list})

Upvotes: 0

Related Questions