63 lines
2.9 KiB
MySQL
63 lines
2.9 KiB
MySQL
|
--------------------------------------------------------
|
||
|
|
-- DDL for View HIERARCHICAL_ENV_GROUP
|
||
|
|
--------------------------------------------------------
|
||
|
|
|
||
|
|
CREATE OR REPLACE FORCE EDITIONABLE VIEW "ENVX_OPER"."HIERARCHICAL_ENV_GROUP" ("LEVEL_ALIAS", "LEVEL_ID", "PARENT_ID", "GROUP_ID", "LEVEL_PATH_ID", "LEVEL_ORDER", "LEVEL_NAME", "LEVEL_STATUS", "DESCRIPTION") AS
|
||
|
|
SELECT UNIQUE 'T' AS level_alias,
|
||
|
|
'T'
|
||
|
|
|| t.id_deploying_tech
|
||
|
|
|| '-'
|
||
|
|
|| g.id_enviroment_type AS level_id,
|
||
|
|
NULL AS parent_id,
|
||
|
|
g.id_enviroment_type AS group_id,
|
||
|
|
g.id_enviroment_type
|
||
|
|
|| '.'
|
||
|
|
|| t.id_deploying_tech AS level_path_id,
|
||
|
|
0 AS level_order,
|
||
|
|
t.deploy_tech_name AS level_name,
|
||
|
|
1 AS level_status,
|
||
|
|
t.deploy_tech_description AS description
|
||
|
|
-------------------- FUNCTIONAL INFO -----------------------------
|
||
|
|
-----------------------------------------------------------------
|
||
|
|
FROM deploying_tech t,
|
||
|
|
preset_env_group g
|
||
|
|
WHERE t.id_deploying_tech = g.id_deploying_tech
|
||
|
|
UNION ALL
|
||
|
|
------------------ HIERARCHICAL INFO ------------------------------
|
||
|
|
SELECT 'G' AS level_alias,
|
||
|
|
'G' || g.id_preset_group AS level_id,
|
||
|
|
'T'
|
||
|
|
|| g.id_deploying_tech
|
||
|
|
|| '-'
|
||
|
|
|| g.id_enviroment_type AS parent_id,
|
||
|
|
g.id_enviroment_type AS group_id,
|
||
|
|
g.id_deploying_tech
|
||
|
|
|| ','
|
||
|
|
|| g.id_preset_group AS level_path_id,
|
||
|
|
0 AS level_order,
|
||
|
|
g.preset_group_name AS level_name,
|
||
|
|
g.preset_group_status AS level_status,
|
||
|
|
g.pres_group_desc AS description
|
||
|
|
FROM preset_env_group g
|
||
|
|
UNION ALL
|
||
|
|
------------------ HIERARCHICAL INFO ------------------------------
|
||
|
|
SELECT 'M' AS level_alias,
|
||
|
|
'M' || g.id_preset_group AS level_id,
|
||
|
|
'G' || m.id_preset_group AS parent_id,
|
||
|
|
g.id_enviroment_type AS group_id,
|
||
|
|
g.id_deploying_tech
|
||
|
|
|| ','
|
||
|
|
|| g.id_preset_group
|
||
|
|
|| ','
|
||
|
|
|| m.id_enviroment AS level_path_id,
|
||
|
|
0 AS level_order,
|
||
|
|
e.enviroment_name AS level_name,
|
||
|
|
e.enviroment_status AS level_status,
|
||
|
|
e.enviroment_alias AS description
|
||
|
|
FROM preset_env_group_members m,
|
||
|
|
preset_env_group g,
|
||
|
|
enviroment e
|
||
|
|
WHERE m.id_preset_group = g.id_preset_group
|
||
|
|
AND e.id_enviroment = m.id_enviroment
|
||
|
|
;
|