SELECT CONNECT_BY_ROOT PARENT_ID ROOT_PARENT_ID, 'LEVEL ' || LEVEL Hierarchy_LEVEL, LPAD(NODE_NAME, length(NODE_NAME) + (10 * (LEVEL))) NODE_NAME_INDENTED, LPAD(CHILD_MODEL, length(CHILD_MODEL) + (10 * (LEVEL))) model_name_indented, Decode(CHILD_MODEL, null, 'No', 'Yes') Sub_Process_Exists, decode ((select node_id from t_gps_models where model_id = child_model_id), null, decode (CHILD_MODEL, null , CHILD_MODEL ,'Attached' ), 'Drawn') DRAWN_OR_ATTACHED, T_GPS_MODELS_HIERARCHY.MODEL_NODE_ID model_node_id, T_GPS_MODELS_HIERARCHY.child_node_id child_node_id, T_GPS_MODELS_HIERARCHY.CATEGORY_ID parent_category_id, T_GPS_MODELS_HIERARCHY.CATEGORY_NAME parent_category_name, T_GPS_MODELS_HIERARCHY.PARENT_ID parent_model_id, T_GPS_MODELS_HIERARCHY.PARENT_MODEL parent_model_name, T_GPS_MODELS_HIERARCHY.PARENT_VERSION parent_model_version, T_GPS_MODELS_HIERARCHY.NODE_ID, T_GPS_MODELS_HIERARCHY.NODE_NAME, T_GPS_MODELS_HIERARCHY.CHILD_CATEGORY_ID, T_GPS_MODELS_HIERARCHY.CHILD_CATEGORY_NAME, T_GPS_MODELS_HIERARCHY.MODEL_ID child_model_id, T_GPS_MODELS_HIERARCHY.CHILD_MODEL child_model_name, T_GPS_MODELS_HIERARCHY.CHILD_MODEL_VERSION, SYS_CONNECT_BY_PATH(T_GPS_MODELS_HIERARCHY.PARENT_ID || '#' || T_GPS_MODELS_HIERARCHY.CHILD_MODEL, '->') full_Path_with_Name, SYS_CONNECT_BY_PATH(T_GPS_MODELS_HIERARCHY.PARENT_ID || '#' ||T_GPS_MODELS_HIERARCHY.NODE_ID, '->') full_Path --T_GPS_MODELS_HIERARCHY.MODEL_ID --m.name,char_length(m.name) + (2* 2), ' '), --, CONNECT_BY_ISCYCLE FROM STATEGPS.T_GPS_MODELS_HIERARCHY T_GPS_MODELS_HIERARCHY -- WHERE T_GPS_MODELS_HIERARCHY.CHILD_MODEL NOT LIKE '%$%' --START WITH T_GPS_MODELS_HIERARCHY.PARENT_ID = 62555 --64405 --62166 CONNECT BY nocycle PRIOR T_GPS_MODELS_HIERARCHY.MODEL_ID = T_GPS_MODELS_HIERARCHY.PARENT_ID --order by SYS_CONNECT_BY_PATH(PARENT_ID || '-' || CHILD_MODEL, '->') union SELECT T_GPS_MODELS.MODEL_ID PARENT_MODEL_ID , 'LEVEL 0' Hierarchy_LEVEL , T_GPS_MODELS.MODEL_NAME NODE_NAME_INTENDED , T_GPS_MODELS.MODEL_NAME MODEL_NAME_INTENDED , 'Main Model' Sub_Process_Exists , 'Drawn' Drawn_or_Attached, null MODEL_NODE_ID, null child_node_id, null parent_category_id , null parent_category_name , null parent_model_id , null parent_model_name , null parent_model_version , null node_id , null node_name , T_CATEGORIES.CATEGORY_ID child_category_id , T_CATEGORIES.CATEGORY_NAME child_category_name , T_GPS_MODELS.MODEL_ID child_model_id , T_GPS_MODELS.MODEL_NAME child_model_name , T_GPS_MODELS.MODEL_STATUS child_model_version , '-' as full_Path_with_name , '-' as full_Node_id_path FROM STATEGPS.T_GPS_MODELS T_GPS_MODELS , STATEGPS.T_CATEGORIES T_CATEGORIES WHERE T_GPS_MODELS.CATEGORY_ID = T_CATEGORIES.CATEGORY_ID