SELECT case
when u.union_type = '' then
(SELECT TO_CHAR(count(1))
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = ''
and a.union_type = ''
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)
WHEN U.UNION_TYPE = '' THEN
U.UNION_NAME
WHEN U.UNION_TYPE = '' THEN
U.PARENT_ORGANIZATION_NAME
end JCGH,
case
when u.union_type = '' OR U.UNION_TYPE = '' then
(SELECT TO_CHAR(count(1))
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = ''
and a.union_type = ''
start with A.UNION_CODE = U.UNION_CODE
CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)
WHEN U.UNION_TYPE = '' THEN
U.UNION_NAME
end FH,
(SELECT count(1)
FROM t_uep_db_employee e
where e.is_enable = ''
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 = ''
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 = ''
and e.employee_sex = ''
and e.IS_UNION_STATE = ''
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = ''
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 = ''
and e.employee_sex = ''
and e.IS_UNION_STATE = ''
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = ''
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 = ''
and e.IS_UNION_STATE = ''
and e.employee_academic = ''
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = ''
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 = ''
and e.IS_UNION_STATE = ''
and e.employee_academic = ''
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = ''
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 = ''
and e.IS_UNION_STATE = ''
and e.employee_academic = ''
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = ''
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 = ''
and e.IS_UNION_STATE = ''
and e.employee_academic = ''
and e.union_code in
(SELECT a.union_code
FROM T_UEP_DB_UNION A
WHERE A.IS_ENABLE = ''
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 = ''
and (e.employee_academic not in ('','', '', '') 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 = ''
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'