oracle8.1.6 select child.keyword_id as child_id, child.heading as child_heading, parent.keyword_id as parent_id, parent.heading as parent_heading, decode(child.keyword_id, null, 0, (select count(*) from bt_bugs where project_id = :package_id and component_id = (select component_id from bt_keyword_component_map where keyword_id = child.keyword_id))) end as num_bugs, (select component_id from bt_keyword_component_map where keyword_id = child.keyword_id) as component_id, (select content_keyword.is_leaf(parent.keyword_id) from dual) as is_leaf from cr_keywords parent, cr_keywords child where parent.parent_id = :component_keyword_id and child.parent_id (+) = parent.keyword_id order by parent.heading, child.heading