1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
|
select /*+ index(a idx_grp_predefini_parent) */
grp_id,
grp_lib,
parent_id,
(select distinct 1 from grp_predefini c where c.parent_id = a.grp_id) hasChildren,
level,
(select distinct 1 from grp_gest c where c.grp_id = a.grp_id and c.client_id = 197792) canModify,
a.dynamic,
decode(hidden, 'yes', 'yes', 'no', 'no', owner_hidden) hidden,
f_concat_list(
cast(
multiset(
select c.nom || ' ' || c.prenom nom
from grp_gest b, pp_client c
where b.grp_id = a.grp_id and
b.client_id = c.client_id and
c.statut_id <> 'D'[color=orange]
union
select c.nom || ' ' || c.prenom nom
from pp_client c
where c.client_id = a.owner_id
order by c.nom, c.prenom[/color]
) as tabString
)
) managers
from
grp_predefini a
connect by
parent_id = prior grp_id
start with
grp_id = 10000 |
Partager