Simple NSQL Query for Resource & OBS Unit

[sbpasc]

We're looking to reconcile our Resource OBS information (find folks without an OBS unit or in the wrong unit), and I need a simple NSQL query that I can plug into the application that will list resources along with their resource obs unit associations. I found a regular SQL query on a different thread but need the NSQL instead. Have zero NSQL experience and would appreciate any assistance. Thanks.

[Tammi Reel-Davis]

Hi,

Try this post to see if it can get you started

7584353

It is looking at the Project OBS but the same applies for the Resource OBS if you know the schema. I tried to find a NSQL query for you from the other threads.

Thanks
Tammi

[Anon Anon]

This is ORACLE syntax and works. Its not very clever though but it will dump the resource IDs and the full obs path for the indicated [color=#ff0000]YOUR OBS ID GOES IN HERE[color] associated to the resource object. (the OBS ID you need to provide is the value in the ID field on teh OBS maintenance screen under General Information).

--

SELECT
@SELECT:DIM:USER_DEF:IMPLIED:R:unique_name :resource_id@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:R:obs_PATH:PATH_NAME@
FROM
(
with t_obs AS
(
SELECT uni.id AS obs_id , SYS_CONNECT_BY_PATH(uni.name, '/') AS obs_PATH
FROM prj_obs_units uni
WHERE uni.type_id = (select id from prj_obs_types where unique_name = '[color=#ff0000]YOUR OBS ID GOES IN HERE[color]')
CONNECT BY PRIOR uni.ID = uni.PARENT_ID
START WITH uni.id in (select id from prj_obs_units where depth = 1)
)
select RR.id , RR.full_name , RR.unique_name , t_obs.obs_id , t_obs.obs_PATH
from srm_resources RR, t_obs
where
( SELECT obu.id
FROM prj_obs_associations oba,
prj_obs_units obu
WHERE obu.id = oba.unit_id
AND oba.table_name = 'SRM_RESOURCES'
AND oba.record_id = RR.id
AND obu.type_id = (select zz.id from prj_obs_types zz where unique_name = '[color=#ff0000]YOUR OBS ID GOES IN HERE[color]')
)
= t_obs.obs_id
)
WHERE @FILTER@

[Deactivated User]

Thanks Dave
It gives the full OBS path for the resources that are associated with the OBS in question if you replace the slash / with some thing else.

NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value

Martti K.

[Anon Anon]

^ I think you are getting that ORA error because you have an OBS unit with a name of (eg) "Things/Stuff" (i.e. containing the slash).

SInce Clarity itself uses the slash when it displays the OBS path on the object instance page... so I don't think its such a good idea to have a slash in the OBS name! cool)

But yes; change the delimter in the NSQL to something else and it will work for your dataset - I ran exactly the code I posted in a 12.1.1 environment on Oracle11g.

[Deactivated User]

I agree with no slashed (and ambersands and such like) in the OBS unit names, but what can you do when they have their OBS already in place and have tens of these.

Martti K.

[Deactivated User]

No NSQL this far just SQL.
Here is a portlet for Oracle which can be easily modified for MS SQL because it does not use any db specific functions..
You can take the NSQL only from the file if you want to or XOG it in into r8.1 or above.
That gives also the resources without any OBS association and you can filter for them. You don't have to tell which OBS type, it gives them all.
Sorry to say not a simple query though,

Martti K.

Attachment(s)

Resource_OBS_portlet.txt   61 KB 1 version

[Deactivated User]

Sivasairam has a couple of posts which take the path from the NBI tables (datamart)

Query on OBS and its Descendents. how to
2281782

SQL to Add OBS of Resources
2281833

Martti K.

[System]

Hi,

Did the suggestions provided help you resolve? If so please mark appropriate posts as Accepted Solution.

Thanks!
Chris

[Robert_Ensinger]

Hi all. Resurrecting and old thread from 2011 here...

 

It's 2015! What is the most efficient and most robust & future proof way to get a concatenated OBS path today in a 13.2 or later datamodel?

 

There's got to be a view or function somewhere, right?

 

I can do this, but this is the wrong view for the job

SELECT distinct(unique_name)
, odf_osum_n27909411_caption
FROM ODF_RESOURCE_V
where unique_name = '<your_unique_name_here>'

 

- NBI tables are the wrong tool for the job since their days are probably numbered with the new datamart.

- I need to add this to a query with multiple Unions, so I would prefer a little encapsulation, make one join on a View or apply a Function instead of duplicating the constructor (modifying Martti's query for MSSQL), in each section of the query.

 

Thanks!

[navzjoshi00]

One of the ways I have seen is to split the OBS and save in custom attributes on the resource object - each attribute saving a level.

 

When getting the data, get the data from those attributes and concatenate the values.

 

NJ

[Anon Anon]

Assuming your db is oracle. you could use "sys_connect_by_path" function to build the path.

 

 

[Robert_Ensinger]

MSSQL here, so I can't use Dave's code above.

 

There *is* an OOTB function that takes Unit_ID

SELECT 
R.ID
, Niku.OBS_UNIT_FULL_PATH(RES_OBSA.UNIT_ID) AS OBS_PATH
,R.UNIQUE_NAME
, R.FULL_NAME
, RES_OBSA.TABLE_NAME
FROM SRM_RESOURCES R
JOIN PRJ_OBS_ASSOCIATIONS RES_OBSA ON R.ID = RES_OBSA.RECORD_ID
WHERE R.UNIQUE_NAME = 'ensi02'

 

PRJ_OBS_ASSOCIATIONS Returns two rows - one is the path I'm looking for, the other looks like Entity/Department.

Valuing OOTB datamodel & simplicity, what's the easiest way to constrain this to my Resource OBS path?

[Robert_Ensinger]

Bah! Reading the function, it's the function that's returning Resource OBS Path and Entity path. I'm MSSQL, so I can't just use the WITH clause in the function (NSQL doesn't like WITHs on MSSQL). One could create a new function or view from this... but there goes my OOTB. Thinking - I'm open to ideas.

[Anon Anon]

SELECT  R.ID  , Niku.OBS_UNIT_FULL_PATH(RES_OBSA.UNIT_ID) AS OBS_PATH  ,R.UNIQUE_NAME  , POU.TYPE_ID , R.FULL_NAME  , RES_OBSA.TABLE_NAME  FROM SRM_RESOURCES R  JOIN PRJ_OBS_ASSOCIATIONS RES_OBSA ON R.ID = RES_OBSA.RECORD_ID  INNER JOIN PRJ_OBS_UNITS POU ON pou.id = RES_OBSA .unit_id WHERE R.UNIQUE_NAME = 'ensi02'

 

Now Add the Type_id filter in the where clause. You can lookup prj_obs_types to find your resource obs.

 

Please educate me if I didn't understand the question correctly.

[Robert_Ensinger]

KABAM! Thanks Sunny! Here it is - concatenated Resource OBS path leveraging the Out of the Box OBS_UNIT_FULL function without having to hard code or duplicate code. The query I'll be putting this in I can trim this down to the two Joins and the OBS_Path Select - concatenated OBS path in 3 lines of code.

 

SELECT
R.ID
, RES_OBSA.UNIT_ID
, Niku.OBS_UNIT_FULL_PATH(RES_OBSA.UNIT_ID) AS OBS_PATH
,R.UNIQUE_NAME
, R.FULL_NAME
, RES_OBSA.TABLE_NAME
, OBSU.TYPE_ID
FROM SRM_RESOURCES R
JOIN PRJ_OBS_ASSOCIATIONS RES_OBSA ON R.ID = RES_OBSA.RECORD_ID
JOIN PRJ_OBS_UNITS OBSU ON OBSU.ID = RES_OBSA.UNIT_ID AND OBSU.TYPE_ID IN (SELECT TYPE_ID FROM PRJ_OBS_OBJECT_TYPES WHERE TABLE_NAME = 'SRM_RESOURCES')
WHERE R.UNIQUE_NAME = 'ensi02'

 

Thanks!

[Anon Anon]

NB if you use the same OBS type against resources as projects (for example), then you need something like RES_OBSA.table_name = 'SRM_RESOURCES' in that code as well - just to protect you against the bad luck where a project record shares the same ID as a resource record and so your code would pick up the random projects OBS details as well as your resource's and prompty fall over.

 

(sorry that might sound complex - it makes sense in my head and I've seen it happen - basically you need to make sure the join on PRJ_OBS_ASSOCIATIONS is fully defined)

[Robert_Ensinger]

Thanks Dave. I appreciate the quality review.

 

Updated:

SELECT
R.UNIQUE_NAME
, R.FULL_NAME
, Niku.OBS_UNIT_FULL_PATH(RES_OBSA.UNIT_ID) AS OBS_PATH
FROM SRM_RESOURCES R
JOIN PRJ_OBS_ASSOCIATIONS RES_OBSA ON R.ID = RES_OBSA.RECORD_ID AND (RES_OBSA.TABLE_NAME = 'SRM_RESOURCES')
JOIN PRJ_OBS_UNITS OBSU ON OBSU.ID = RES_OBSA.UNIT_ID AND OBSU.TYPE_ID IN (SELECT TYPE_ID FROM PRJ_OBS_OBJECT_TYPES WHERE TABLE_NAME = 'SRM_RESOURCES')
WHERE R.UNIQUE_NAME = 'ensi02'

 

[Deactivated User]

What is the big deal in views and functions? Are they upgrade proof? How frequently will you be querying that?

If used this for ten years and I think it is still version proof and works in MS SQL and Oracle and I've posted it before.

 

select

-- only resources with a defined link to an OBS unit

-- displays the full OBS unit path

srm_resources.id,

srm_resources.unique_name,

srm_resources.full_name,

prj_obs_types.id Type_id,

PRJ_obs_types.unique_name Type_unique,

PRJ_obs_types.name OBS_type_name,

prj_obs_units.ID OBS_ID,

prj_obs_units.Unique_name OBS_unique,

prj_obs_units.name OBS_name,

OBS_path.OBS_full_PATH

from

srm_resources,

prj_obs_units,

prj_obs_associations,

prj_obs_types,

(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

 

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

) as OBS_path

where

prj_obs_associations.record_id=srm_resources.id

AND prj_obs_associations.UNIT_ID=prj_obs_units.id

and prj_obs_associations.table_name= 'SRM_RESOURCES'

and prj_obs_types.id=prj_obs_units.type_id

 

and OBS_path.OBS_unit_id=prj_obs_units.id

and OBS_path.OBS_type_nAME=PRJ_obs_types.name

 

-- and srm_resources.is_active = 1

-- and prj_resources.prisopen = 1

-- and prj_resources.prtrackmode = 2

 

and srm_resources.last_name ='First_name'

and srm_resources.first_name ='Last_name'

 

 

and PRJ_obs_types.unique_name = 'OBS name'

 

Order by OBS_ID desc, srm_resources.full_name

[Anon Anon]

Can you use PRJ_OBS_UNITS.TYPE_ID to filter?