MYSQL with clause in Jasperreport

[imrohithsnair]

MYSQL with clause in Jasperreport  and Jasper Soft Studio

WITH
  tb1 AS (SELECT a, b FROM table1),
  tb2 AS (SELECT c, d FROM table2)
SELECT b, d FROM tb1 JOIN tb2
WHERE tb1.a = tb2.c;

I need to use query like above example in MySql dataset, can i able to use?

I have tried like this but i couldn't able.

Thank You in advance

[jabrown]

You need to configure the SQL validator on Jaspersoft Server to accept WITH as valid syntax.  TIBCO's default installation of Jaspersoft Server does not accept the WITH syntax to help prevent against SQL injection.  More on SQL injection from the Jaspersoft Server Security Guide. You can add WITH to the valid SQL syntax by doing the following.

•     Open jasperserver-pro/WEB-INF/classes/esapi/validation.properties
•     Append the following to the ValidSQL rule:
•     Validator.ValidSQL=(?is)^\s*(select|with)\s+^;+$
•     Close the file and restart the server. The ValidSQL rule will now allow for the words "select" or "with" to be at the beginning of a query.