postgresql7.1 FIX ME CONNECT BY select t.object_type_two, t.role_two as role, acs_rel_type__role_pretty_name(t.role_two) as role_pretty_name, acs_object_type__pretty_name(t.object_type_two) as object_type_two_name, ancestor_rel_types.object_type as ancestor_rel_type from acs_rel_types t, acs_object_types obj_types, acs_object_types ancestor_rel_types where t.rel_type = :rel_type and t.rel_type = obj_types.object_type and ancestor_rel_types.supertype = 'relationship' and ancestor_rel_types.object_type in ( select object_type from acs_object_types start with object_type = :rel_type connect by object_type = prior supertype ) FIX ME DECODE (USE SQL92 CASE) FIX ME OUTER JOIN FIX ME CONNECT BY select DISTINCT decode(groups.group_id, null, case when persons.person_id = null then 'INVALID' else persons.first_names || ' ' || persons.last_name end, groups.group_name) as party_name, p.party_id from (select o.object_id as party_id from acs_objects o, (select object_type from acs_object_types start with $start_with connect by prior object_type = supertype) t where o.object_type = t.object_type) p, (select element_id from group_element_map where group_id = :group_id and rel_type = :rel_type UNION ALL select to_number(:group_id) ) m, (select object_id from all_object_party_privilege_map where party_id = :user_id and privilege = 'read') perm, (select party_id from rc_parties_in_required_segs where group_id = :group_id and rel_type = :rel_type) pirs $scope_query, groups, persons where p.party_id = m.element_id(+) and m.element_id is null and p.party_id = perm.object_id and p.party_id = pirs.party_id $scope_clause and p.party_id = groups.group_id(+) and p.party_id = persons.person_id(+)