Subject Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE
Author Mark Rotteveel
On 30-5-2018 14:41, Gabor Boros mlnglsts@... [firebird-support] wrote:
> 2018. 05. 29. 19:08 keltezéssel, Omacht András aomacht@...
> [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 real data. It's easy to
> reproduce just execute the next script for additional test data.

The problematic part is probably

SELECT, t.parent_id
FROM table1 t
WHERE t.parent_id = (select from table1 root where
root.parent_id is null)

Recursive CTEs always make my head hurt, but if I understand the intent
correctly, you should replace it with:

select, t.parent_id
from table1 t
inner join table1 root on t.parent_id = and root.parent_id is null

or alternatively:

select, t.parent_id
from table1 t
where exists (select 1 from table1 root where = t.parent_id and
root.parent_id is null)


Mark Rotteveel