Variables in Query

[shisou]

Hello,

 

I am currently trying to create a report that queries based off of a formatted parameter.

 

The parameter will be a string formatted as follows:

'exp1','exp2','exp3'    etc...

 

I need it to work with my sql WHERE clause so I format it using replace() as follows:

$P{parameter}.replace("," , " OR column = ")

and I store that in a variable, $V{vari}

so that my Query looks like this...

 

WHERE column = $V{vari}

 

So hard coded it would look like this...

 

WHERE column = exp1 OR column = exp2 OR column = exp3

and I'm getting an error "Unrecognized SQL escape 'vari' at ..."

 

Anyone know what's wrong or if there's another way I could do this?

 

Thanks in advance!

 

[shisou]

I had a feeling I'd figure it out shortly after posting :P

 

What I ended up doing was creating a second parameter with

$P{parameter}.replace( "," , " OR column = ")

 

and then passed that parameter to the query as there WHERE clause and that worked nicely...

 

Thanks for taking the time to read this and hopefully this can help someone else down the road.

[mdahlman]

shisou,

 

It sounds like you found a good solution. Also keep in mind that the $X syntax does the same thing.

 

For example use this code: ... WHERE $X{IN, MyColumn, MyParameter} AND ...

 

Define MyParameter as a Collection of Strings.

 

The generate SQL will be like this:
... WHERE MyColumn in ( 'expr1', 'expr2', 'expr3' ) AND ...

 

Regards,
Matt