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.id, t.parent_id
FROM table1 t
WHERE t.parent_id = (select root.id 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 ti.id, t.parent_id
from table1 t
inner join table1 root on t.parent_id = root.id and root.parent_id is null

or alternatively:

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

Mark

--
Mark Rotteveel