Education & Training
Services & Support
to create and rate content, and to follow, bookmark, and share content with other members.
Query on OBS and its Descendents. how to?
Question asked by
on Sep 12, 2008
on Sep 15, 2008 by DeclanKenny
Show 0 Likes
Hi,I'm trying to write a query to list all my projects in certain OBS and its descendants but I'm unable to because of the number of levels and sub levels. does anyone have any solution for this? thank you very much for your help.Waely
No one else has this question
Mark as assumed answered
This content has been marked as final.
Show 2 comments
(Required, will not be published)
Sep 13, 2008 1:41 AM
select srmr.unique_name ProjectID, srmr.name ProjectNameFROM SRM_PROJECTS SRMR, PRJ_OBS_aSSOCIATIONS PRAWHERE PRA.TABLE_NAME='SRM_PROJECTS' AND PRA.RECORD_ID=SRMR.ID AND PRA.UNIT_ID IN (SELECT PRU.ID FROM PRJ_OBS_UNITS PRU PRJ_OBS_TYPES PRT WHERE PRU.TYPE_ID=PRT.ID AND PRT.UNIQUE_NAME= START WITH PRU.NAME= CONNECT BY PRIOR PRU.PARENT_ID=PRU.ID)
Show 0 Likes
Sep 15, 2008 3:20 PM
You don't need to use a START WITH, CONNECT BY clause. The table NBI_DIM_OBS_FLAT does all that work for you.This table gives you all descendants of the PARENT_OBS_UNIT_ID value (including a record for the parent itself), so by joining to this table and having that field as a required parameter, you will always get every project associate to that node or any descendant.here's some sample code, its for 8.1, for 7.5.3 - replace INV_INVESTMENTS and INV_PROJECTS with SRM_P.. and PRJ_P.. SELECT II.CODE AS PROJECT_ID ,II.NAME AS PROJECT_NAMEFROM INV_INVESTMENTS IIINNER JOIN INV_PROJECTS IPON II.ID = IP.PRIDINNER JOIN PRJ_OBS_ASSOCIATIONS POAON II.ID = POA.RECORD_IDAND POA.TABLE_NAME = 'SRM_PROJECTS'INNER JOIN NBI_DIM_OBS_FLAT NDOFON POA.UNIT_ID = NDOF.CHILD_OBS_UNIT_ID WHERE NDOF.PARENT_OBS_UNIT_ID = 5002097; Message Edited by DeclanKenny on 09-15-2008 02:20 PM [left]
Show 1 Like
Retrieving data ...
[WEBCAST] CA IDMS Database Maintenance: Recover the Database (2 of 2) – November 16, 2018 @ 11 a.m. ET
Benefit plan replacement by XOG
WS-Security and Live Invocation
Agile Central Database Connector (Postgres, MS SQL Server) 1.1.0
How do I query for a list of User Stories that have attachments?
Re: Query to list values of all OBS Levels Units