$X IN a substring of a column value

[chaddn]

I need to construct a query that uses the $X IN function but I don't want the parameter value to match the entire value of the field, only a selected portion of it.  So ideally the query would include the following in the where clause:

$X{IN,left(columnname,4),paramname}

So basically I want to select where the left 4 characters of the column match my parameter values (it's a collection).  Of course this doesn't work because Jasper is expecting only the column name and gets hung up on the comma before the 4 (it thinks 4 is the parameter name). Does anyone have any ideas how to accomplish this?  I've looked through the documentation for $X and haven't found any examples of this scenario.

Thanks,

Chadd

[lisacbeaton]

Could you try something like:

 

 

SELECT * FROM (

SELECT left(columnname,4) as NewColumn

)

WHERE $X{IN, NewColumn, paramname}

[lisacbeaton]

I apologize for the formatting... I hope you get what I'm getting at there.

[lucianc]

$X accepts by default semicolons and vertical bars as token separators.  Note that you should only a single separator character, you can't combine them in the same $X clause.

HTH,

Lucian

[palee]

Has anyone have any luck with solving this problem?  I have the same questions.

[lucianc]

Did you see the answer below?