prehlb Posted February 8, 2010 Share Posted February 8, 2010 I have a challenge for those using the $X{} clause in SQL environments.The definitive guide (Third Edition) has the following definition for the $X{} clause: JasperReports has built-in support for two clause functions: IN and NOTIN. Bothfunctions expect two parameters: The SQL column or expression to be used as the left side in the IN/NOT IN clause. The name of the report parameter that will supply the values list. The value of this parameter can either be a java.util.Collection instance or an object or primitive Java array. Please note the bolded text: "or expression". I bring this up because I have an interest in having the actual SQL column name be wrapped in a function for checking against the collection for matches.For example, say we have courses with course ID's. One column name is COURSE_ID. But, some of the course ID's begin with certain characters, say "T", "K", "U" and "A" to define say a type of course. The report on courses might need to break them into two subreports - one for types T and K and the other subreport for U and A.Now, the SQL query would normally look like:1. SELECT COURSE_ID, COURSE_NAME FROM COURSE WHERE $X{IN,COURSE_ID, CoursePrefixes}The parameter $P{CoursePrefixes} is defined as a java.util.Collection and contains ['T','K'], which are the first characters to match against in the COURSE_ID field.The problem above is that it is not looking at just the first character of the COURSE_ID field, it's looking at all the characters which is not what I need it to do. I need to have the SQL IN Clause resolve to:WHERE LEFT(COURSE_ID,1) IN ('T','K')which means the query should look like:2. SELECT COURSE_ID, COURSE_NAME FROM COURSE WHERE $X{IN,LEFT(COURSE_ID,1), CoursePrefixes}But query 2 fails with syntax issues around the "1)".So, my question is how would you specify an "expression" for a columnName in the $X{} clause such as above?I have come up with work-arounds such as using MS SQL CTE's and such, but I wanted to know if there's any documentation for how to formulate an expression for the columnName.Thank you,Bill Link to comment Share on other sites More sharing options...
lucianc Posted February 23, 2010 Share Posted February 23, 2010 You'll have to use a different separator for $X{..}. The separators supported by default are , ; |$X{IN;LEFT(COURSE_ID,1); CoursePrefixes} should work.Regards,Lucian Link to comment Share on other sites More sharing options...
Voltrone Posted March 18, 2013 Share Posted March 18, 2013 iReport designer automatically replaces ; separators with , when you save the query so this workaround doesn't seem to work. Link to comment Share on other sites More sharing options...
cjasper Posted September 12, 2013 Share Posted September 12, 2013 yes you are right the ; is not working but the bar | is working.I tested this with postgres Link to comment Share on other sites More sharing options...
jerome.parot Posted May 26, 2014 Share Posted May 26, 2014 Nop, this workaround doesn't work with postgres. I tested the | or ; syntax and jasper still replaces it with , Hope someone can help me doing this. Regards, Link to comment Share on other sites More sharing options...
palee Posted June 25, 2014 Share Posted June 25, 2014 Has anyone solved this? I have similar problem. Link to comment Share on other sites More sharing options...
ktalarico Posted November 17, 2014 Share Posted November 17, 2014 I am having same problem. Would love to know if there is a solution. Link to comment Share on other sites More sharing options...
sunilkumarsheoran Posted April 16, 2015 Share Posted April 16, 2015 used simple text editor to edit the query within the .jrxml file as suggested (not using IReport Designer's query editor which was the one who replaced even after editing ';' or '|' with ',' ) and it worked. for example: select * from table where $X{ IN ;(setup_id,lead_id);_PARAMETER_LEAD} may be i am using old IReport. Link to comment Share on other sites More sharing options...
AK777 Posted September 20, 2017 Share Posted September 20, 2017 this comments helped in my case... 11 years after you have posted it! cheers Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now