Ignite unable to understand recursive query.

[PunxsutawneyPhil3]

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/