SQL & PL/SQL

Hi,

Hoping someone can assist me - have a hierarchical set-up going on a table using the whole START WITH and CONNECT BY clauses, which I am using to set-up a vertical-aligned menu system that can expand out to the right, depending if a menu option has children and total number of Levels. Also, as part of the menu option, if a parent has children, I also display a '>' symbol to specify this.

My question is, I am using CONNECT_BY_ISLEAF to determine whether a menu option is a leaf or not but I also have a column in my hierarchical table that specifies whether the a menu option is active or not.

So when I have a case of a parent/child set-up in my menu, so that the ISLEAF value for Parent is 0 and Child is 1, but I have actually made the Child menu option inactive, my '>' symbol still displays at the parent level, even though the child record for this parent is inactive.

Any idea how I can check this alongside with the CONNET_BY_ISLEAF value, to prevent the '>' symbol appearing even though this parent menu option has an inactive child record?

Basically want something that, if a child record exists based on ISLEAF value but child record is inactive, then ignore this leaf record and pretend it doesn't actually exists.

Hope this makes sense.

Thanks.
Tony.