Recursive Query

[forever.sumitsharma]

I need to write the recursive query to fetch the data in my select query.

but it was giving me error with that. I'm writing this query

WITH RECURSIVE all_reports AS (

        SELECT id, manager_user_id, user_group_id

        FROM users

        WHERE manager_user_id=4

          UNION ALL

        SELECT c.id, c.manager_user_id, c.user_group_id

        FROM all_reports ar, users c

        WHERE c.manager_user_id=ar.id

      )

 

is it possible to write the recursive query? if yes please suggest me what I'm doing wrong.

 

Thanks in advance

 

[joel.carlson]

I only got this working in version 6.1.0. You have to add With to the validation.properties file. By default it only allows select and call. So go to 

/{yourinstalldirectory}/jasperreports-server-cp-6.1.0/apache-tomcat/webapps/jasperserver/WEB-INF/classes/esapi

There you will find validation.properties file, search for Validator.ValidSQL= and add with where you see select and call. I use linux so I am not sure your platform. I also tried this in version 5.5.0 but couldn't get it to work. It works fine for me in the latest version and I use a lot of CTE queries now to build temp tables in db2 because it is easier that way than declaring temp tables.