Using SELECT statement inside an IF Else condition in Jasper reports SQL query in Jasper server
Posted on September 19, 2014 at 4:33am
I have the following sample query:
SELECT top 10 * FROM Person
SELECT top 10 * FROM Person WHERE Id < 20
When i create a report with above query using Ireport , the report works fine.
But when i upload the same report to jasper server and try to run the report, it is throwing the below error.
ERROR Validator,report1 subreports #1:493 - Invalid SQL:An error has occurred. Please contact your system administrator. (6632), SQL: IF(1=1)SELECT top 10 * FROM PersonELSESelect top 10 * FROM Person WHERE Id < 20.
Does Jasper server supports IF..Else conditions outside the SELECT statements.?
Do any one know about this error?
Thanks in advance
Posted on September 25, 2014 at 4:07am
After searching for around 4 days, i found a solution.
Jasper Server allows the SQL queries that starts with only "SELECT" keyword. So in order to make other queries work, we have to modify a regular expression of "Validator.ValidSQL" that is present in "webapps/jasperserver/WEB-INF/classes/esapi/security-config.properties/validation.properties" location.
I have modified this regular expression to support 'IF' conditions and made it work.
Got this solution from jasper community forum: