View HLPDTEST.STATEGPS.T_GPS_INDENTED_MODELS_V | Generated by SchemaSpy |
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Sat Jul 02 01:05 IST 2022 |
View Definition:
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
Possibly Referenced Tables/Views:
![]() ![]() |