Using SELECT statement inside an IF Else condition in Jasper reports SQL query in Jasper server

[vinayt]

I have the following sample query:

IF(1=1)      Select  top 10 * from Person     ELSE      Select top 10 * from Person where Id < 20[/code]

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.[/code]

Does Jasper server supports IF..Else conditions outside the SELECT statements.?

Do any one know about this error?

Thanks in advance

[vinayt]

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:

http://community.jaspersoft.com/questions/800208/stored-procedures-works-ireport-does-not-work-jasperserver.