SELECT case
when u.union_type = '1' then
(SELECT TO_CHAR(count(1))
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = '1'
and a.union_type = '2'
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)
WHEN U.UNION_TYPE = '2' THEN
U.UNION_NAME
WHEN U.UNION_TYPE = '3' THEN
U.PARENT_ORGANIZATION_NAME
end JCGH,
case
when u.union_type = '1' OR U.UNION_TYPE = '2' then
(SELECT TO_CHAR(count(1))
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = '1'
and a.union_type = '3'
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)
WHEN U.UNION_TYPE = '3' THEN
U.UNION_NAME
end FH,
(SELECT count(1)
FROM t_uep_db_employee e
where e.is_enable = '1'
and e.IS_UNION_STATE = 0
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = '1'
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) HY,
(SELECT count(1)
FROM t_uep_db_employee e
where e.is_enable = '1'
and e.employee_sex = '1'
and e.IS_UNION_STATE = '0'
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = '1'
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) man,
(SELECT count(1)
FROM t_uep_db_employee e
where e.is_enable = '1'
and e.employee_sex = '0'
and e.IS_UNION_STATE = '0'
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = '1'
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) woman,
(SELECT count(1)
FROM t_uep_db_employee e
where e.is_enable = '1'
and e.IS_UNION_STATE = '0'
and e.employee_academic = '0'
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = '1'
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) ZZ,
(SELECT count(1)
FROM t_uep_db_employee e
where e.is_enable = '1'
and e.IS_UNION_STATE = '0'
and e.employee_academic = '1'
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = '1'
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) ZK,
(SELECT count(1)
FROM t_uep_db_employee e
where e.is_enable = '1'
and e.IS_UNION_STATE = '0'
and e.employee_academic = '2'
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = '1'
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) BK,
(SELECT count(1)
FROM t_uep_db_employee e
where e.is_enable = '1'
and e.IS_UNION_STATE = '0'
and e.employee_academic = '3'
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = '1'
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) SSYJS,
(SELECT count(1)
FROM t_uep_db_employee e
where e.is_enable = '1'
and (e.employee_academic not in ('0','1', '2', '3') or e.employee_academic is null)
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = '1'
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) QT
FROM T_UEP_DB_UNION U
WHERE U.UNION_CODE = '234E45F0077881AAE0430AA3034681AA'
