Recursive SQL and information on different level

Question

Is it possible to display, in the same query, information concerning different level of recursivity ?

select LEVEL, ae2.CAB, ae2.NIVEAU, ae2.ENTITE, ae2.ENTITE_PARENT, ae2.libelle
from my_table ae2
where ae2.NIVEAU = 2
start with ae2.cab = 'XXX'
connect by prior ae2.entite_parent = ae2.entite

With this query I have (let's say) 4 levels of information about the entity above root 'XXX'

Is it possible to display root information at the same time?

Answer 1

Yes, it's possible to use the CONNECT_BY_ROOT operator. For instance, if you wanted the cab of the parent your query would be:

select connect_by_root cab
      , level, cab, niveau, entite, entite_parent, libelle
   from my_table
  where niveau = 2
  start with cab = 'XXX'
connect by prior entite_parent = entite

You have to use a new operator for each column you want to select. You won't get information from a "different" level of recursivity using this operator, only from the root. If you want more you'll have to use recursive subquery factoring.