I'm trying to recursively get some data using connect by, which for each row I need to know if in a different tree contains a specific item.
So I have this [stripped down] query:
select m.id, m.name,
(select (
with recursiveMetaClass as (
select mb.name
from meta mb
start with mb.id = m.id
connect by prior mb.metaclass = mb.id and mb.metaclass <> mb.id
) select name from recursiveMetaClass where name in ('config_group', 'config_item')
) from dual) as basetype
from meta m
inner join meta_rel mr on mr.rel2 = m.id
start with m.name = 'store_sys'
connect by prior m.id = mr.rel1
order siblings by item_order;
This errors though with ORA-00904 "M"."ID": invalid identifier on m.id in start with in my nested query. If I replace with query with:
select m.id, m.name,
(select (
with recursiveMetaClass as (
select mb.name
from meta mb
start with mb.id = 1004
connect by prior mb.metaclass = mb.id and mb.metaclass <> mb.id
) select name from recursiveMetaClass where name in ('config_group', 'config_item')
) from dual) as basetype
from meta m
inner join meta_rel mr on mr.rel2 = m.id
start with m.name = 'store_sys'
connect by prior m.id = mr.rel1
order siblings by item_order;
It works (just gives wrong results).
How can I either get m.id into that start with, or refactor this to work? I also tried using some nested selects and doing a where on rownumber = 1 order by level desc, however I get the exact same error when trying to reference m.id.
Thank you!
Can't you simplify this query to the following?
select m.id, m.name,
( select mb.name
from meta mb
where name in ('config_group', 'config_item')
start with mb.id = m.id
connect by prior mb.metaclass = mb.id and mb.metaclass <> mb.id
) as basetype
from meta m
inner join meta_rel mr on mr.rel2 = m.id
start with m.name = 'store_sys'
connect by prior m.id = mr.rel1
order siblings by item_order;