[SAS] 디시전트리 룰 추출 쿼리

[오라클에 디시전트리 소스를 테이블 형태로 업로드한 후 실행]


WITH V1 AS
(
        SELECT  *
        FROM    Z_DT_RULE_SC4_M05 /* 디시전트리 소스 테이블 */
)
SELECT  MAX(CORP)     AS CORP
        , MAX(MODEL)    AS MODEL
        , DIV AS SEQ
        , MAX(NODE)     AS NODE
        , MAX(CNT)      AS CNT
        , MAX(Y_PER)    AS Y_PER
        ,   MAX(CASE WHEN PNO=1  THEN SCRIPT2 END)
          ||MAX(CASE WHEN PNO=2  THEN ' '||SCRIPT2 END)
          ||MAX(CASE WHEN PNO=3  THEN ' '||SCRIPT2 END)
          ||MAX(CASE WHEN PNO=4  THEN ' '||SCRIPT2 END)
          ||MAX(CASE WHEN PNO=5  THEN ' '||SCRIPT2 END)
          ||MAX(CASE WHEN PNO=6  THEN ' '||SCRIPT2 END)
          ||MAX(CASE WHEN PNO=7  THEN ' '||SCRIPT2 END)
          ||MAX(CASE WHEN PNO=8  THEN ' '||SCRIPT2 END)
          ||MAX(CASE WHEN PNO=9  THEN ' '||SCRIPT2 END)
          ||MAX(CASE WHEN PNO=10 THEN ' '||SCRIPT2 END)
          AS SCRIPT
FROM    (
        SELECT  A.*
                , CASE
                  WHEN DIV <> THEN_S
                  THEN ROW_NUMBER() OVER (PARTITION BY DIV ORDER BY NO)
                  END AS PNO
        FROM    (
                SELECT  A.*
                        , SUM(FST_ROW) OVER (ORDER BY NO) AS DIV
                        , NVL(SUM(THEN_ROW) OVER (ORDER BY NO),0) AS THEN_S
                        , REPLACE(
                          REPLACE(
                          REPLACE(
                          REPLACE(
                          REPLACE(
                          REPLACE(
                          REPLACE(
                          REPLACE(
                          REPLACE(
                          REPLACE(
                          SCRIPT
                          , '  ', ' ')
                          , '  ', ' ')
                          , '  ', ' ')
                          , '  ', ' ')
                          , '  ', ' ')
                          , '  ', ' ')
                          , '  ', ' ')
                          , '  ', ' ')
                          , '  ', ' ')
                          , '  ', ' ')
                          AS SCRIPT2
                FROM    (
                        SELECT  ROWNUM AS NO
                                , CASE WHEN SUBSTR(SCRIPT,1,2) = 'IF' THEN 1 END AS FST_ROW
                                , CASE WHEN SUBSTR(SCRIPT,1,4) = 'THEN' THEN 1 END AS THEN_ROW
                                , CASE WHEN SUBSTR(SCRIPT,1,4) = 'NODE' THEN TRIM(SUBSTR(SCRIPT,10,10)) END AS NODE
                                , CASE WHEN SUBSTR(SCRIPT,1,1) = 'Y'    THEN TRIM(SUBSTR(SCRIPT,10,7))  END AS Y_PER
                                , CASE WHEN SUBSTR(SCRIPT,1,4) = 'N   '
                                        AND REPLACE(SCRIPT, '%', '@') = SCRIPT THEN TRIM(SUBSTR(SCRIPT,10,10))  END AS CNT
                                , A.*
                        FROM    V1 A
                        ) A
                ) A
        ) A
GROUP BY DIV
;