이렇게 해보세요..

with dept(dept_code,dept_parent) as
(
select root.dept_code,root.dept_parent
from km_deptinfo root
where dept_code = '00011'
union all
select sub.dept_code,sub.dept_parent
from km_deptinfo sub, dept super
where sub.DEPT_PARENT = super.dept_code
)

SELECT COUNT(USR_ID)
FROM KM_USERINFO
WHERE USR_STATUS != 3 AND USR_CLASS != 2 AND
USR_DEPTCODE IN (select dept_code
   from dept
   where dept_parent !='-1');

이게 맞나요?

with dept(DEPT_CODE, DEPT_PARENT) as (values
('0001','0000'),
('0011','0001'),
('0012','0001'),
('0111','0011'),
('0112','0011'),
('0121','0012'),
('0122','0012')),
user (USER_DEPT_CODE,USER_ID) as (values
('0011','A'),
('0011','B'),
('0012','C'),
('0012','D'),
('0012','E'),
('0111','F'),
('0111','G'),
('0112','H'),
('0112','I'),
('0121','J'),
('0121','K'),
('0121','L'),
('0122','M'),
('0122','L'),
('0122','O')),

r_dept(dept_code,dept_parent) as
(
select dept_code, dept_parent
from dept
where dept_code = '0001'
union all
select sub.dept_code,sub.dept_parent
from dept sub, r_dept super
where sub.dept_parent = super.dept_code
)

--select * from r_dept;
SELECT user_id,COUNT(*)
  FROM user 
 WHERE USER_DEPT_CODE in (select distinct dept_code from r_dept)
group by user_id;

감사합니다...ㅠㅠ 디뎌 됩니다..ㅠㅠ

근데 오라클의 connect by 를 db2 에서 함수로 만들수 잇나여??

만드는게 가능한지여?