Clarity

Hello

Is there any query that some can share to get all values of the units from all levels of all OBSs please?

Thanks

This is required for data cleansing and redefining the OBS correctly in-line with the business needs.

Hi Mayank,

 prj_obs_units table will have this information. 

Hi Mayank,

Try this query - You will have to change the obs_type_name to the name of your OBS. 

You will have to change in the below places

obs_type_name='Department OBS'

obs_type_name='Finance Reporting OBS'

select srm.unique_name Res_id,
(select user_name from cmn_sec_users where id = srm.user_id) User_id,
(case when srm.is_active =0 then 'Inactive' else 'Active' END) res_status,
'Financiall Disabled' financial_status,
(select level2_name from nbi_dim_obs where obs_unit_id in(select unit_id from prj_obs_associations where table_name='SRM_RESOURCES' and record_id=srm.id)
and obs_type_name='Department OBS') as BU_Group,
(select path from nbi_dim_obs where obs_unit_id in(select unit_id from prj_obs_associations where table_name='SRM_RESOURCES' and record_id=srm.id)
and obs_type_name='Department OBS') as DEPT_OBS,
(select path from nbi_dim_obs where obs_unit_id in(select unit_id from prj_obs_associations where table_name='SRM_RESOURCES' and record_id=srm.id)
and obs_type_name='Finance Reporting OBS') as RES_OBS
from srm_resources srm
where srm.unique_name not in
(select srm.unique_name res_id from pac_mnt_resources pac,srm_resources srm,prj_resources prj
where pac.active=1 and srm.id = pac.id and prj.prid = pac.id and prj.prid=srm.id and prj.prisrole=0)
and srm.user_id is not null
UNION
select srm.unique_name Res_id,
(select user_name from cmn_sec_users where id = srm.user_id) User_id,
(case when srm.is_active =0 then 'Inactive' else 'Active' END) res_status,
'Financiall Enable' financial_status,
(select level2_name from nbi_dim_obs where obs_unit_id in(select unit_id from prj_obs_associations where table_name='SRM_RESOURCES' and record_id=srm.id)
and obs_type_name='Department OBS') as BU_Group,
(select path from nbi_dim_obs where obs_unit_id in(select unit_id from prj_obs_associations where table_name='SRM_RESOURCES' and record_id=srm.id)
and obs_type_name='Department OBS') as DEPT_OBS,
(select path from nbi_dim_obs where obs_unit_id in(select unit_id from prj_obs_associations where table_name='SRM_RESOURCES' and record_id=srm.id)
and obs_type_name='Finance Reporting OBS') as RES_OBS
from srm_resources srm
where srm.unique_name in
(select srm.unique_name res_id from pac_mnt_resources pac,srm_resources srm,prj_resources prj
where pac.active=1 and srm.id = pac.id and prj.prid = pac.id and prj.prid=srm.id and prj.prisrole=0)
and srm.user_id is not null

Thanks,

Jerin

For Oracle you could also use

SELECT
 prj_obs_types.name OBS_type_nAME,
  prj_obs_types.UNIQUE_NAME Type_unique_name,
  (TRIM (LEADING '/' FROM
(NVL(Parent10.name,'') || NVL(Parent9.name|| '/','') || NVL(Parent8.name+'/' ,'') || NVL(Parent7.name ||
 '/','') ||
NVL(Parent6.name || '/','') ||NVL(Parent5.name || '/','') || NVL(Parent4.name || '/','') || NVL(Parent3.name
 || '/','') ||
NVL(Parent2.name || '/','') || NVL(Parent1.name || '/','') || prj_obs_units.name))) OBS_full_PATH,
 
  prj_obs_units.NAME Unit_name,
  prj_obs_units.UNIQUE_NAME Unit_unique_name,
 prj_obs_units.id OBS_unit_id,
 prj_obs_units.DEPTH
 from
 prj_obs_types,
prj_obs_units left join prj_obs_units Parent1 on prj_obs_units.parent_id=Parent1.id
left join prj_obs_units Parent2 on Parent1.parent_id=Parent2.id
left join prj_obs_units Parent3 on Parent2.parent_id=Parent3.id
left join prj_obs_units Parent4 on Parent3.parent_id=Parent4.id
left join prj_obs_units Parent5 on Parent4.parent_id=Parent5.id
left join prj_obs_units Parent6 on Parent5.parent_id=Parent6.id
left join prj_obs_units Parent7 on Parent6.parent_id=Parent7.id
left join prj_obs_units Parent8 on Parent7.parent_id=Parent8.id
left join prj_obs_units Parent9 on Parent8.parent_id=Parent9.id
left join prj_obs_units Parent10 on Parent9.parent_id=Parent10.id
where
prj_OBS_units.type_id=prj_obs_types.id

You could also search

Help with Query for OBS 

Query on OBS and its Descendents. how to? 

https://communities.ca.com/message/7883546?commentID=7883546#comment-7883546 

OBS Query 

Hi,

I think that query can help you:

select
dep.id,
dep.obs_unit_id,
dep.departamento,
dep.nivel
from
(
select
dep.id,
dep.obs_unit_id,
sys_connect_by_path (dep.description,'\') departamento,
connect_by_root obs.depth nivel
from
niku.departments dep
join niku.prj_obs_units obs
on
dep.obs_unit_id = obs.id
connect by prior dep.id = dep.parent_department_id
)
dep
where
dep.nivel = 1

Hello

Sorry that I did not mention that I use SQL Server DB.

I could not run getting following error on SQL Server:

Msg 195, Level 15, State 10, Line 11

'sys_connect_by_path' is not a recognized built-in function name.

Please advise.

Thanks

Hi,

sys_connect_by_path equivalent function in SQL Server is the STUFF function,but it has a different syntax. You need to modify your SQL a bit to achieve this.

For MS SQL try

SELECT

prj_obs_types.name OBS_type_Name,
(
ISNULL(Parent10.name,'') + ISNULL(Parent9.name + '/','') + ISNULL(Parent8.name+'/' ,'') + ISNULL(Parent7.name + '/','') +
ISNULL(Parent6.name + '/','') +ISNULL(Parent5.name + '/','') + ISNULL(Parent4.name + '/','') + ISNULL(Parent3.name + '/','') +
ISNULL(Parent2.name + '/','') + ISNULL(Parent1.name + '/','') + prj_obs_units.name) OBS_full_PATH
, prj_obs_units.id OBS_unit_id
, prj_obs_units.name OBS_unit_name
from

prj_obs_types,
prj_obs_units left join prj_obs_units Parent1 on prj_obs_units.parent_id=Parent1.id
left join prj_obs_units Parent2 on Parent1.parent_id=Parent2.id
left join prj_obs_units Parent3 on Parent2.parent_id=Parent3.id
left join prj_obs_units Parent4 on Parent3.parent_id=Parent4.id
left join prj_obs_units Parent5 on Parent4.parent_id=Parent5.id
left join prj_obs_units Parent6 on Parent5.parent_id=Parent6.id
left join prj_obs_units Parent7 on Parent6.parent_id=Parent7.id
left join prj_obs_units Parent8 on Parent7.parent_id=Parent8.id
left join prj_obs_units Parent9 on Parent8.parent_id=Parent9.id
left join prj_obs_units Parent10 on Parent9.parent_id=Parent10.id
where
prj_OBS_units.type_id=prj_obs_types.id

-- order by whatever you want

The output is not as neat for the blank names as from the Oracle query

Try that query:

WITH MyCTE(type_id, id, obs_name) AS (
SELECT
type_id,
id,
CAST(
[name] AS varchar(1024)
)
FROM
prj_obs_units
WHERE
parent_id IS NULL
UNION ALL
SELECT
c.type_id,
c.id,
CAST(
p.obs_name + '\' + c.[name] AS varchar(1024))
FROM prj_obs_units AS c INNER JOIN MyCTE AS p ON c.parent_id = p.id)

SELECT type_id, id, obs_name
FROM MyCTE

Source: sys_connect_by_path equivalent in sql - CodeProject 

perfect mate !

Many thanks.