AnsweredAssumed Answered

Diff between nbr of resources retrieved from OBS versus srm_resources SQL

Question asked by AlexSch on Nov 13, 2012
Latest reply on Nov 19, 2012 by Chris_Hackett
Why would there be a difference between the number of resources retrieved from the (2) queries below? One query looks at the srm_resources table ONLY and pulls all resources that are actve and have a resource_manager id assocaited with that row.

select MGR.FULL_NAME manager_name,MGR.UNIQUE_NAME, EMP.FULL_NAME resource_name
from NIKU.SRM_RESOURCES emp,
NIKU.SRM_RESOURCES mgr
where EMP.MANAGER_ID = MGR.USER_ID
and MGR.UNIQUE_NAME <> 'admin'
and EMP.IS_ACTIVE = 1
order by MGR.FULL_NAME, EMP.FULL_NAME

The second query pulls all resources associated with/for a specfic RM from the PRJ_OBS_ASSOCIATIONS and NBI_DIM_OBS_FLAT tables. The query only pulls active resources, too. This query pulls back many more rows than the above query, but my question is WHHY? Shouldn't they both pull back the same number of rows?

btw...in the below query I'm only pulling back resources for (1) RM....

Select
SRM_RESOURCES.LAST_NAME||' , '||SRM_RESOURCES.FIRST_NAME,
SRM_RESOURCES.ID, SRM_RESOURCES.UNIQUE_NAME,
SRM_RESOURCES2.LAST_NAME||' , '||SRM_RESOURCES2.FIRST_NAME,
SRM_RESOURCES2.ID, SRM_RESOURCES2.UNIQUE_NAME
from NIKU.SRM_RESOURCES SRM_RESOURCES,
NIKU.SRM_RESOURCES SRM_RESOURCES2,
NIKU.PRJ_OBS_ASSOCIATIONS OBS_ASSOC,
NIKU.PRJ_OBS_ASSOCIATIONS OBS_ASSOC2,
NIKU.NBI_DIM_OBS_FLAT OBS_FLAT
where OBS_ASSOC.record_id = srm_resources.id
and OBS_ASSOC.table_name = 'SRM_RESOURCES'
and OBS_FLAT.CHILD_OBS_UNIT_ID = obs_assoc.unit_ID
and OBS_FLAT.OBS_Type_ID = 5000002
and obs_assoc2.table_name = 'SRM_RESOURCES'
and OBS_ASSOC2.unit_ID = OBS_FLAT.PARENT_OBS_UNIT_ID
and OBS_ASSOC2.record_ID = SRM_RESOURCES2.ID
and upper(srm_resources2.unique_name)= 'BUTLERD'
and SRM_RESOURCES.IS_ACTIVE = 1
order by SRM_RESOURCES.LAST_NAME

Outcomes