Clarity PPM

Expand all | Collapse all

Simple NSQL Query for Resource & OBS Unit

  • 1.  Simple NSQL Query for Resource & OBS Unit

    Posted 09-15-2011 02:59 PM
    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.


  • 2.  RE: Simple NSQL Query for Resource & OBS Unit

    Posted 09-15-2011 04:07 PM
    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


  • 3.  RE: Simple NSQL Query for Resource & OBS Unit

    Posted 09-16-2011 04:20 AM
    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@


  • 4.  RE: Simple NSQL Query for Resource & OBS Unit

    Posted 09-16-2011 09:23 AM
    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.


  • 5.  RE: Simple NSQL Query for Resource & OBS Unit

    Posted 09-16-2011 09:49 AM
    ^ 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.


  • 6.  RE: Simple NSQL Query for Resource & OBS Unit

    Posted 09-16-2011 03:43 PM
    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.


  • 7.  RE: Simple NSQL Query for Resource & OBS Unit

    Posted 09-19-2011 10:02 AM
      |   view attached
    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)



  • 8.  RE: Simple NSQL Query for Resource & OBS Unit

    Posted 09-16-2011 11:40 AM
    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.


  • 9.  RE: Simple NSQL Query for Resource & OBS Unit

    Posted 09-26-2011 05:57 PM
    Hi,

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

    Thanks!
    Chris


  • 10.  Re: Simple NSQL Query for Resource & OBS Unit

    Posted 04-08-2015 10:51 AM

    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!



  • 11.  Re: Simple NSQL Query for Resource & OBS Unit

    Posted 04-08-2015 11:05 AM

    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



  • 12.  Re: Simple NSQL Query for Resource & OBS Unit

    Posted 04-08-2015 11:18 AM

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

     

     



  • 13.  Re: Simple NSQL Query for Resource & OBS Unit

    Posted 04-08-2015 11:43 AM

    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.

    results.jpg

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



  • 14.  Re: Simple NSQL Query for Resource & OBS Unit

    Posted 04-08-2015 11:49 AM

    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.



  • 15.  Re: Simple NSQL Query for Resource & OBS Unit

    Posted 04-08-2015 11:56 AM

    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.



  • 16.  Re: Simple NSQL Query for Resource & OBS Unit

    Posted 04-08-2015 12:07 PM

    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!



  • 17.  Re: Simple NSQL Query for Resource & OBS Unit

    Posted 04-08-2015 12:17 PM

    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)



  • 18.  Re: Simple NSQL Query for Resource & OBS Unit

    Posted 04-08-2015 01:12 PM

    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'
    
    
    
    

     

    results.jpg



  • 19.  Re: Simple NSQL Query for Resource & OBS Unit

    Posted 04-08-2015 04:50 PM

    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



  • 20.  Re: Simple NSQL Query for Resource & OBS Unit

    Posted 04-08-2015 11:49 AM

    Can you use PRJ_OBS_UNITS.TYPE_ID to filter?