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

Reply via email to