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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
