Clarity

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

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)  

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]