create view1

– 未解决
CREATE OR REPLACE VIEW DWD_NPW_PRP_FLOW_BANK_D
(
PRODUCT_NAME,
FLOW_NAME,
START_BANKS,
END_BANK
) AS
SELECT tpp.PRODUCT_NAME,a.FLOW_NAME,START_BANKS,END_BANK
FROM
(
SELECT DISTINCT FLOW_NAME,REGEXP_SUBSTR(START_BANKS, ‘[^,]+’, 1, LEVEL) AS START_BANKS,END_BANK
FROM (
SELECT
a.FLOW_NAME, a.START_BANKS,a.END_BANK
FROM
MES_PROD_MODEL_USER.T_PRP_FLOW_BANK a
WHERE
DATA_STATUS >0 AND DEPLOY_STATUS=4
) a
CONNECT BY PRIOR FLOW_NAME = FLOW_NAME AND LEVEL <= REGEXP_COUNT(START_BANKS, ‘[^,]+’) + 1
AND PRIOR DBE_RANDOM.GET_VALUE IS NOT NULL
AND REGEXP_SUBSTR(START_BANKS, ‘[^,]+’, 1, LEVEL) IS NOT NULL
START WITH LEVEL = 1
) a
INNER JOIN MES_PROD_MODEL_USER.T_PRP_FLOW tpf
ON tpf.FLOW_NAME = a.FLOW_NAME
AND tpf.DATA_STATUS != 0
AND tpf.OBJECT_TYPE =10
INNER JOIN MES_PROD_MODEL_USER.T_PRP_PRODUCT tpp
ON tpp.FLOW_ID = tpf.ID
AND tpp.DATA_STATUS != 0
AND tpp.DEPLOY_STATUS = 4
AND tpp.PRODUCT_TYPE = 30
;