Hello! As far as my understanding goes, Ignite does not support recursive CTE.
You may still try running it as a local query by setting local=true in your connection/query settings. Regards, -- Ilya Kasnacheev чт, 15 апр. 2021 г. в 02:33, PunxsutawneyPhil3 <rwoodr...@niksun.com>: > I am having a problem with the following query which results in this error: > > javax.cache.CacheException: Failed to parse query. Column "S.TAGID" not > found; SQL statement: > > From reading it looks like the reason for this is explained in this > <https://apacheignite-sql.readme.io/docs/distributed-joins> > documentation, > and is that Ignite cannot resolve the columns of reportIdList after the > Inner Join. > > Is there any way to restructure this query so it can be understood by > Ignite? > > > > WITH RECURSIVE reportIdList AS > ( > SELECT reportId, tagId, owner > FROM "MyReportPojoCache".MyReportPojo > WHERE id = ANY (SELECT id > FROM "MyOtherReportPojoCache".MyOtherReportPojo > WHERE owner IS NOT NULL > AND isManage IS TRUE > AND type = 'tag-group') > UNION > SELECT m.reportId, m.tagId, m.owner > FROM "MyReportPojoCache".MyReportPojo m > INNER JOIN reportIdList s ON s.tagId = m.reportId > AND s.owner IS NOT NULL > AND s.owner != 'admin' > AND s.owner = m.owner > ) > SELECT qpIntId > FROM "MyReportPojoCache".MyReportPojo > WHERE (report_id, owner) IN (SELECT report_id, owner FROM reportIdList) > > > link StackOverflow post > > https://stackoverflow.com/questions/67098705/how-to-rewrite-recursive-sql-query-to-work-with-ignite-sql-queries > < > https://stackoverflow.com/questions/67098705/how-to-rewrite-recursive-sql-query-to-work-with-ignite-sql-queries> > > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >
