Clarity

I have the following SQL written for a custom resource portlet.  What I need to finally add is the OBS that the resource is assigned or associated to.  Our company wants the format showing all OBS levels, for example.  LEvel1\level2\level3\level4\etc..   Is this possible? And if so, can someone help me ad OBS data to this report and map it to the corresponding resource?  Thanks   SELECT C.ID, C.USER_NAME, C.LAST_NAME, C.FIRST_NAME, CASE R.PERSON_TYPE WHEN '300' THEN 'Employee' WHEN '301' THEN 'Contractor' ELSE 'NULL' END as 'Employee Type', CASE C.USER_STATUS_ID WHEN '200' THEN 'Active' WHEN '201' THEN 'In-Active' ELSE 'Locked' END as 'USER STATUS', N.ROLE_NAME, R.DATE_OF_HIRE, N.MANAGER_LAST_NAME, N.MANAGER_FIRST_NAMEFROM CMN_SEC_USERS C, NBI_RESOURCE_CURRENT_FACTS N, SRM_RESOURCES RWHERE C.ID = N.RESOURCE_ID AND C.ID = R.ID ORDER BY C.LAST_NAME  

Have you looked under general discussion at Query on OBS and its Descendants, how to? Maybethat'll help you. Martti K.

SELECT C.ID, C.USER_NAME, C.LAST_NAME, C.FIRST_NAME, N.ROLE_NAME, R.DATE_OF_HIRE, N.MANAGER_LAST_NAME, N.MANAGER_FIRST_NAME, (select nbi.path from nbi_dim_obs nbi, prj_obs_associations pra where pra.record_id=r.id          and pra.table_name='SRM_RESOURCES' and nbi.obs_unit_id=pra.unit_id and nbi.obs_type_name= ) path FROM CMN_SEC_USERS C, NBI_RESOURCE_CURRENT_FACTS N, SRM_RESOURCES R WHERE C.ID = N.RESOURCE_ID AND C.ID = R.ID ORDER BY C.LAST_NAME This shows complete obs path of resource.  ThanksSiva 

Siva, thank you very much!

Below is the 'updated' code to get the true OBS results for resources who reside in a specific OBS..    SELECT R.ID, R.USER_ID, R.UNIQUE_NAME, R.LAST_NAME, R.FIRST_NAME, N.ROLE_NAME, R.DATE_OF_HIRE, N.MANAGER_LAST_NAME, N.MANAGER_FIRST_NAME, (select nbi.path from nbi_dim_obs nbi, prj_obs_associations pra where pra.record_id=r.id and pra.table_name='SRM_RESOURCES' and nbi.obs_unit_id=pra.unit_id and nbi.obs_type_name='OBS NAME GOES IN HERE') path FROM CMN_SEC_USERS C, NBI_RESOURCE_CURRENT_FACTS N, SRM_RESOURCES R WHERE R.ID = N.RESOURCE_ID AND C.ID = R.ID  

how can i add to this query to find out the number of resources who have timesheets in that obs?

Thx