-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 각각 매핍변수로 선언

SELECT SUB1,SUB2,SUB3,SUB4,SUB5,SUB6,SUB7,SUB8,SUB9,SUB10,SUB11,SUB12,SUB13,SUB14,SUB15
    FROM(SELECT 
            MAX(DECODE(rowno,1,aa)) SUB1
           ,MAX(DECODE(rowno,2,aa)) SUB2
           ,MAX(DECODE(rowno,3,aa)) SUB3
           ,MAX(DECODE(rowno,4,aa)) SUB4
           ,MAX(DECODE(rowno,5,aa)) SUB5
           ,MAX(DECODE(rowno,6,aa)) SUB6
           ,MAX(DECODE(rowno,7,aa)) SUB7
           ,MAX(DECODE(rowno,8,aa)) SUB8
           ,MAX(DECODE(rowno,9,aa)) SUB9
           ,MAX(DECODE(rowno,10,aa)) SUB10
           ,MAX(DECODE(rowno,11,aa)) SUB11
           ,MAX(DECODE(rowno,12,aa)) SUB12
           ,MAX(DECODE(rowno,13,aa)) SUB13
           ,MAX(DECODE(rowno,14,aa)) SUB14
           ,MAX(DECODE(rowno,15,aa)) SUB15
        FROM(
             SELECT SUBSTR( RCVDATA, 
                    INSTR (RCVDATA, '|', 1, LEVEL) + 1, 
                    INSTR (RCVDATA, '|', 1, LEVEL + 1) - 
                    INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa,
                    ROWNUM AS rowno
               FROM (SELECT '|' || SUBS_VALUE ||'|' RCVDATA
                           ,ROWNUM
                       FROM TB_INST_INPUT
                       --WHERE INSTID = '@{EMS_M_ID}'
                       ) 
                    CONNECT BY LEVEL<= LENGTH(RCVDATA) - 
                    LENGTH(REPLACE(RCVDATA, '|')) - 1
        )
        WHERE ROWNUM <= '15'
    )
WHERE ROWNUM = 1


-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 한개의 데이터로 조합


SELECT a1||a2||a3||a4||a5||a6||a7||a8||a9||a10||a11||a12||a13||a14  totaldata
    FROM(SELECT MAX(DECODE(rowno,1,aa)) a1
           ,MAX(DECODE(rowno,2,aa)) a2
           ,MAX(DECODE(rowno,3,aa)) a3
           ,MAX(DECODE(rowno,4,aa)) a4
           ,MAX(DECODE(rowno,5,aa)) a5
           ,MAX(DECODE(rowno,6,aa)) a6
           ,MAX(DECODE(rowno,7,aa)) a7
           ,MAX(DECODE(rowno,8,aa)) A8
           ,MAX(DECODE(rowno,9,aa)) a9
           ,MAX(DECODE(rowno,10,aa)) a10
           ,MAX(DECODE(rowno,11,aa)) a11
           ,MAX(DECODE(rowno,12,aa)) a12
           ,MAX(DECODE(rowno,13,aa)) a13
           ,MAX(DECODE(rowno,14,aa)) a14
        FROM(SELECT SUBSTR( RCVDATA, 
                    INSTR (RCVDATA, '|', 1, LEVEL) + 1, 
                    INSTR (RCVDATA, '|', 1, LEVEL + 1) - 
                    INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa
                    ,ROWNUM AS rowno
               FROM (SELECT '|' || SUBS_VALUE || '|' RCVDATA
                           ,ROWNUM
                       FROM TB_INST_INPUT) 
                    CONNECT BY LEVEL<= LENGTH(RCVDATA) - 
                    LENGTH(REPLACE(RCVDATA, '|')) - 1)
    WHERE ROWNUM <= '14'
    )
    
    
-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 리스트 데이터로 조합    

SELECT SUBSTR( RCVDATA, 
INSTR (RCVDATA, '|', 1, LEVEL) + 1, 
INSTR (RCVDATA, '|', 1, LEVEL + 1) - 
INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa
FROM (SELECT '|' || SUBS_VALUE || '|' RCVDATA FROM TB_INST_INPUT) 
CONNECT BY LEVEL<= LENGTH(RCVDATA) - 
LENGTH(REPLACE(RCVDATA, '|')) - 1;