​Thanks Key for the response.
I 'm trying to achieve , oracle's hierarchical CONNECT BY NOCYCLE PRIOR in
h2 databse hierarchical queries i.e. with clause queries / common table
expression,
When there is a loop in the data ,with clause queries in h2 database is
running for long and brings the out of memory error .
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, coalesce(LINK.NAME || '/', '') || FOLDER.NAME, LEVEL1
+ 1
FROM LINK INNER JOIN FOLDER ON LINK.ID = FOLDER.PARENT
)
SELECT * FROM LINK WHERE NAME IS NOT NULL ORDER BY ID
No when there is some data bringing looping scenario ,
*INSERT INTO FOLDER VALUES(2, 'Loop Data', 5);*
After inserting this loop data , when i above recursive query in h2 it
results in out of memory:
[image: Inline image 1]
Is there any equivalent for this h2 database ??
On Thu, Jul 6, 2017 at 7:10 AM, Kerry Sainsbury <[email protected]> wrote:
> I can't help in any way, but suggest you post plain text rather than
> screen shots -- it's a lot easy to copy/paste the faulty SQL ;-)
>
> On 4 July 2017 at 21:45, Ashif Ahamed <[email protected]> wrote:
>
>>
>>
>>
>> Can anyone help on solving this scenario in H2 recursive queries..
>>
>>
>>
>>
>> <https://lh3.googleusercontent.com/-XsZtkqyH9lc/WVtjTySNyNI/AAAAAAAAAhg/v-Fu3lMdDc4t3hFb_lKC9S6FNCbmR4ZIwCLcBGAs/s1600/Data_With_Loop.JPG>
>>
>> <https://lh3.googleusercontent.com/-41qLFCFXFyc/WVtjM78VR_I/AAAAAAAAAhc/ri8cW9bTYSADwWZjPRFZY-EKQkYHcPYPQCLcBGAs/s1600/Data_WithOut_Loop.JPG>
>>
>>
>>
>> <https://lh3.googleusercontent.com/-41qLFCFXFyc/WVtjM78VR_I/AAAAAAAAAhc/ri8cW9bTYSADwWZjPRFZY-EKQkYHcPYPQCLcBGAs/s1600/Data_WithOut_Loop.JPG>
>>
>> <https://lh3.googleusercontent.com/-41qLFCFXFyc/WVtjM78VR_I/AAAAAAAAAhc/ri8cW9bTYSADwWZjPRFZY-EKQkYHcPYPQCLcBGAs/s1600/Data_WithOut_Loop.JPG>
>>
>> <https://lh3.googleusercontent.com/-41qLFCFXFyc/WVtjM78VR_I/AAAAAAAAAhc/ri8cW9bTYSADwWZjPRFZY-EKQkYHcPYPQCLcBGAs/s1600/Data_WithOut_Loop.JPG>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at https://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>
--
Ashif Ahamed . A
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
