using $V{var} in SQL

[jreevessingletouch.net]

 

Hi all,

FIRST POST!!! :)

I have a multi-select input control which I'm accessing as a java.util.Collection parameter.  The items chosen by the user for this input parameter will end up going into an SQL "IN" statement like so:

blah IN ('item1','item2',...,'itemN')

What I've done is created a variable which has the following expression:

new java.lang.String(($P{market} == null)?"":" market.market_description IN " + $P{market}.toString().replaceFirst("\[","('").replaceFirst("\]","')").replaceAll(",","','") + " AND ")

I've verified that the variable contains the correct text, but trying to insert it into the SQL query using $V!{market_list} didn't work.  I tried mirroring $V{market_list} as a parameter, and that didn't work.

Am I going about this completely wrong?  Help!

[contentengineer]

 This is the code we use for piping in a list of document references as a parameter in SQL:

1. $P{list} - this is the original field i.e. "INV100000,INV100001,INV100123"

 

2. $P{listparsed} - the parsed list with default value expression:

$P{list}.replace(",","','")

 

3. In query:

select * from <table> where docid in ('$P!{listparsed}')

 

Works a dream on about seven reports we run thro' Jasperserver and iReport

 

[jreevessingletouch.net]

Thanks, Rob!  That was exactly what I needed.