Converting Collection parameter value to SQL-IN clause

[rault]

This is really a postgres question but I thought I'd post it here because it starts with a Collection object in a report parameter. If I take that collection and say if my collection contains values 1,2,3 and 4 and I do this:

$P{myCollection}.toString()

The string output from that looks like [1, 2, 3, 4]

I'd like to take that and convert it to something like:

WHERE myTableField IN ('1', '2', '3', '4')

This is similar to what Jasper seems to do in the report query anyway but I have a use case where I need to do this myself. I have this select for postgres that gets me close but it doesn't quite work:

select translate(translate(translate('[1, 2, 3, 4]', '[', ''''), ']', ''''), ', ', ',''') as value

Which outputs this in Postgres 9.1:

'1,'2,'3,'4'

I'm still missing the second quote on the inner numbers and I don't understand why because the last translate function should handle this correctly. I think anyway. Has anyone else done this?

 

[rault]

I posted this on forums.devshed.com and recieved a reply within 30 minutes:

http://forums.devshed.com/postgresql-help-21/convert-string-to-sql-in-syntax-884001.html#post2744594

 

Basically you have to call 3 different functions to get this to work:

select string_agg(''''||i||'''', ',') from unnest(regexp_split_to_array(trim('[1, 2, 3, 4]','[] '), ',s')) i

 First you split the string to an array, then unnest the elements while wrapping them in single quotes.

Post Edited by rault at 02/13/2012 15:25

[rault]

Found a better solution and posted it here:

 

http://jasperforge.org/plugins/espforum/view.php?group_id=102&forumid=103&topicid=95869

[mdahlman]

In most cases it is better to use this:

WHERE $X{ myCollection, myTableField, IN }

That way JasperReports takes care of converting the Collection into an IN clause for you. If myCollection happens to be empty, then "1=1" gets substituted in so that the next clause still gets ANDed correctly to it.

 

[rault]

True, I think the syntax is:

 

$X{IN, TableFieldName, CollectionParameterName}

 

I had a case where I was passing a Collection in a hyperlink and the items in the hyperlink contained commas. Since the Collection gets turned into a string for the query string parameter the drill through report was picking it up and creating the wrong items. Let's take a simple example, let's say I have a Collection with a single item in it like so:

 

[Joe's Crab Shack, LLC]

 

The field in the report is a hyperlink on Joe's Crab Shack, LLC to a second report that shows sales detail. What we should be doing is passing the IDs of the records but in our database we have names that come from locations and a "global" name that each name gets grouped together as. Normally I would have put the global name in its own table but I wasn't the one that designed the database and I don't think "we" can change it anytime soon, if at all.

So the hyperlink parameter comes over in the querystring like:

 

http://mydomainname.com/whatever/flowwhatever?reportid=whatever&MYCOLLECTION=[Joe's Crab Shack, LLC]

 

The second report picks up the value for the collection and because it seems to be using the built in parser from Java (I'm guessing about that...) it causes the Colelction to contain two items like so:

 

[Joe's Crab Shack, LLC]

Item1 = Joe's Crab Shack

Item2 = LLC

 

Which is no longer the item I was trying to send over on the drill thorugh. But it's true, in most cases using the $X{IN} is better because you don't have to think about it.

Using the spring framework method, you can choose what delimiter to use on your own:

 

org.springframework.util.StringUtils.collectionToDelimitedString($P{MyCollection}, "MYDELIMITER")

 

Which can be a poundsign "#" or a combination of unique charaters "#!#". Whatever will work with the data you are passing. In the MYCOLLECTION parameter in the second report you can use the Collection.join method to convert it back into a Collection. I bet Jasper is doing this, but using the built in toString() method for Collections which has a comma delimiter built in.

 

The best method though would be to pass IDs and just use $X{IN} but that just doesn't work for me in this case.

Post Edited by rault at 04/23/2012 19:57