Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

2018. 06. 04. 16:17 keltezéssel, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] írta: > I understand that data from real database are restricted. > But what is the query plan for my CTE? A VIEW based on a VIEW then JOIN-ed with a VIEW which contains the CTE. So the plan is

Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

I understand that data from real database are restricted.But what is the query plan for my CTE?Do you have an index on column id_parent? I am really interested about it in your environment i use it every day. Regards,Karol Bieniaszewski null

Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

2018. 06. 01. 17:33 keltezéssel, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] írta: > Hi, > > can you show some sample? > And what indexes have  you created? > Can you show plan for both queries and real queries itself? > What filter "where" are you using? > > I am really

Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

Hi, can you show some sample?And what indexes have  you created?Can you show plan for both queries and real queries itself?What filter "where" are you using? I am really interested because i have compared both queries with much more populated tables and i have got better results time, fetches

Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

Hi, better is walk throught tree -- WITH RECURSIVE R_TREE AS ( SELECT TT.ID, TT.ID AS TOP_PARENT FROM TABLE1 TT WHERE TT.PARENT_ID IS NULL UNION ALL SELECT TT.ID, RT.TOP_PARENT FROM TABLE1 TT JOIN R_TREE RT ON RT.ID =

Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

2018. 05. 30. 16:51 keltezéssel, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] írta: > Recursive CTEs always make my head hurt, but if I understand the intent > correctly, you should replace it with: > > select ti.id, t.parent_id > from table1 t > inner join table1 r

RE: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

Hi Gábor! I see, you have many root elements. Then Mark gave the perfect solution: WITH RECURSIVE tree AS ( SELECT t.id, t.parent_id FROM table1 t inner join table1 root on root.id = t.parent_id WHERE root.parent_id is null UNION ALL

Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

On 30-5-2018 14:41, Gabor Boros mlngl...@bgss.hu [firebird-support] wrote: > 2018. 05. 29. 19:08 keltezéssel, Omacht András aoma...@mve.hu > [firebird-support] írta: >> Hi Gabor! >> >> You can do it with recursive cte: > > > First of all, thank you very much! But... > > I got "multiple rows in

Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

2018. 05. 30. 16:04 keltezéssel, Omacht András aoma...@mve.hu [firebird-support] írta: > Hi Gábor! > > Now I can only test on 2.5.8, and it works: András, I tried now with 2.5.8 and got same error as with 3.0.3. Your TABLE1 contains all 12 rows? Gabor

RE: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE 2018. 05. 29. 19:08 keltezéssel, Omacht András aoma...@mve.hu [firebird-support] írta: > Hi Gabor! > > You can do it with recursive cte: First of all, thank you very much! But... I got

Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

2018. 05. 29. 19:08 keltezéssel, Omacht András aoma...@mve.hu [firebird-support] írta: > Hi Gabor! > > You can do it with recursive cte: First of all, thank you very much! But... I got "multiple rows in singleton select multiple rows in singleton select" (yes, double) error message with my

RE: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

FROM tree t András -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-supp...@yahoogroups..com] Sent: Tuesday, May 29, 2018 6:25 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE Hi All, I have a table