Ashif Ahamed wrote: > I 'm trying to achieve , oracle's hierarchical CONNECT BY NOCYCLE PRIOR in > SQLite database hierarchical queries i.e. with clause queries(common table > expression) > > When there is a loop in the data ,with clause queries in SQLite database is > running for long with infinite loops
When you use UNION instead of UNION ALL, duplicate rows are ignored. > WITH LINK(ID, NAME, LEVEL1) AS ( > SELECT ID, NAME, 0 LEVEL1 FROM FOLDER WHERE PARENT IS NULL > UNION ALL > SELECT FOLDER.ID <http://folder.id/>, coalesce(LINK.NAME > <http://link.name/> || '/', '') || FOLDER.NAME <http://folder.name/>, > LEVEL1 + 1 > FROM LINK INNER JOIN FOLDER ON LINK.ID <http://link.id/> = FOLDER.PARENT > ) > SELECT * FROM LINK WHERE NAME IS NOT NULL ORDER BY ID The level value would prevent the duplicate detection. Do you really need it? You could add a LIMIT clause, which at least prevents the infinite loop. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users