[SOLVED] Dynamic SQL statement with $X syntax

[hainz]

Hi, i've been working with JasperReport for about half a year now and i stumbled across my first problem which i am not able to solve by myself after a couple days.

For a lot of my queries i use the $X{IN, <column_name>, <collection_param>} syntax several times in the WHERE clause to select a subset after joining.

Now i would like to completely ignore / remove the JOIN adn WHERE clause if the collection is empty (= no element selected from input control). No problem removing the JOIN clause, but i am having trouble with the WHERE clause. I was thinking about something like: $P{WHERE_CLAUSE} = IF($P{COLLECTION}.isEmpty(), "", "WHERE $X{IN, column, $P{COLLECTION}}") and use $P!{WHERE_CLAUSE} - but i did not get this type of query to work. I figured it is because the query string does not take the $X parameter as an actual string, but other approches with different datatypes failed too.

The only other way i could think of was using a subreport with unique query string for every possible combination of empty and not empty collections, which would create A LOT of overhead for further development.

 

Is it somehow possible to create a parameter that can either be empty or include a WHERE clause with $X{} parameter?

 

Thanks!

[lucianc]

You don't need $P{..} inside $X{IN, ..}, the correct expression for WHERE_CLAUSE is IF($P{COLLECTION}.isEmpty(), "", "WHERE $X{IN, column, COLLECTION}")

And if in other cases you do need a literal "$P{..}" inside an expression, you need to escape it as "$$P{..}" so that it doesn't get interpreted as a parameter value placeholder.  E.g. IF($P{Value} == null, "", "WHERE column = $$P{Value}")

Regards,

Lucian