view3

CREATE OR REPLACE VIEW EAP_RECIPE_PATH
(
  EAP_AREA,
  EQPID,
  SUB_EQP,
  NAME,
  VALUE,
  TOOL_ID
) AS
WITH TOOL_IDS AS (
SELECT EAP_AREA, SERVER_IP, APP_NAME, REGEXP_SUBSTR(TOOL_IDS, '[^,]+', 1, LEVEL) AS TOOL_ID, TOOL_IDS_s
FROM(
SELECT b.LOCATION_NAME AS EAP_AREA, b.SERVER_IP, SUBSTR(a.APP_NAME, LENGTH (a.APP_NAME)-LENGTH (b.NAME)+1, LENGTH (b.NAME)) AS APP_NAME, b.TOOL_IDS, REGEXP_COUNT(b.TOOL_IDS, ',') + 1 AS TOOL_IDS_s
FROM T_EAPMGR_APPLICATION a
LEFT OUTER JOIN T_EAPMGR_EAP_CONFIG b ON
a.TOOL_CONFIG_VERSION = b.VERSION
AND SUBSTR(a.APP_NAME, LENGTH (a.APP_NAME)-LENGTH (b.NAME)+1, LENGTH (b.NAME)) = b.NAME
WHERE a.STATUS = 1
AND b.STATUS = 2
) t
CONNECT BY LEVEL <= t.TOOL_IDS_s
AND PRIOR sys_guid() IS NOT NULL
AND PRIOR t.EAP_AREA = t.EAP_AREA
AND PRIOR t.SERVER_IP = t.SERVER_IP
AND PRIOR t.APP_NAME = t.APP_NAME
)
, INITSETTING_ID AS (
SELECT a.NAME, a.ID, a.INIT_SETTING_ID, b.EAP_AREA, b.APP_NAME, b.TOOL_ID
FROM T_EAPMGR_EAP_TOOL a
LEFT OUTER JOIN TOOL_IDS b ON a.ID = b.TOOL_ID
WHERE b.TOOL_ID IS NOT NULL
)
SELECT c.EAP_AREA, c.APP_NAME AS EQPID,  c.NAME AS SUB_EQP, b.name, a.VALUE, c.TOOL_ID
FROM T_EAPMGR_EAP_TOOL_DETAIL a
LEFT OUTER JOIN T_EAPMGR_EAP_TOOL_DICT b ON
a.DICT_ID = b.ID
LEFT OUTER JOIN INITSETTING_ID c ON
a.DATA_ID = c.INIT_SETTING_ID
WHERE b.name LIKE '%_recipe_%'
AND c.INIT_SETTING_ID IS NOT NULL
ORDER BY c.EAP_AREA, c.APP_NAME, c.NAME
;