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 Consider the below sample data: (With Out Loop) CREATE TABLE FOLDER(ID INT , NAME VARCHAR(255), PARENT INT); INSERT INTO FOLDER VALUES(1, null, null); INSERT INTO FOLDER VALUES(2, 'src', 1); INSERT INTO FOLDER VALUES(3, 'main', 2); INSERT INTO FOLDER VALUES(4, 'org', 3); INSERT INTO FOLDER VALUES(5, 'test', 2); 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 Above query works fine because there is no loop in data. When there is some data bringing looping scenario : *INSERT INTO FOLDER VALUES(2, 'Loop Data', 5);* After inserting this loop data , when i trigger the above recursive query in SQLite it keeps on running without bringing any results. Note: In oracle database , this kind of scenario is handled by connect by nocycle prior or cycle column set is_cycle to '1' default '0' How to handle this scenario in SQLite? Can anyone look on into this.. -- Ashif Ahamed . A _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
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 [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
In reply to this post by Ashif Ahamed
On 06/07/2017 08:08, Ashif Ahamed wrote:
> > When there is some data bringing looping scenario : > > *INSERT INTO FOLDER VALUES(2, 'Loop Data', 5);* > > After inserting this loop data , when i trigger the above recursive query > in SQLite it keeps on running without bringing any results. > > Note: In oracle database , this kind of scenario is handled by connect by > nocycle prior or cycle column set is_cycle to '1' default '0' > > How to handle this scenario in SQLite? > > Can anyone look on into this.. You could use one of the three techniques suggested in this document. https://www.dropbox.com/s/5tsh7lod3oqv3h5/Tuto19-Recursive-programming.pdf?dl=0 J-L Hainaut _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
Free forum by Nabble | Edit this page |