3,491
社区成员
发帖
与我相关
我的任务
分享
--物化视图创建
CREATE MATERIALIZED VIEW MV_RFF
BUILD IMMEDIATE
AS
SELECT MST_ID, TRANSLATE (LTRIM (TEXT, '/'), '*/', '*,') RESEARCHERLIST
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY MST_ID ORDER BY MST_ID,
LVL DESC) RN,
MST_ID, TEXT
FROM (SELECT MST_ID, LEVEL LVL,
SYS_CONNECT_BY_PATH (C1_1154_2,'/') TEXT
FROM (SELECT MST_ID, C1_1154_2 AS C1_1154_2,
ROW_NUMBER () OVER (PARTITION BY MST_ID ORDER BY MST_ID,C1_1154_2) X
FROM EDIFACT_SEG_RFF
ORDER BY MST_ID, C1_1154_2) A
CONNECT BY MST_ID = PRIOR MST_ID AND X - 1 = PRIOR X))
WHERE RN = 1
ORDER BY MST_ID;