CONNECT_BY_ISLEAF with Conditions

Question

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 CONNECT_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. TT.

Answer 1

SELECT  *
FROM    table
CONNECT BY
        parent = PRIOR id
        AND active = 1

This will select a child only if it's active, if that's what you want.

Note that this query will return CONNECT_BY_ISLEAF = 1 for the items that do not have active children, and they will probably be treated as endpoints in your design.