3depth rowspan 구하기

SELECT a1.*,
           CASE
             WHEN ROW_NUMBER() OVER(PARTITION BY a1.prod_cd
            ORDER BY a1.prod_cd)=1 THEN COUNT(*) OVER(PARTITION BY a1.prod_cd)
             ELSE 0
           END rowspan_cnt,
           CASE
             WHEN ROW_NUMBER() OVER(PARTITION BY a1.prod_cd, a1.detail1_cause_cd
            ORDER BY a1.prod_cd, a1.detail1_cause_cd)=1 THEN COUNT(*) OVER(PARTITION BY a1.prod_cd, a1.detail1_cause_cd)
             ELSE 0
           END rowspan_cnt2
    FROM   (SELECT prod_cd,
                   detail1_cause_cd,
                   detail1_sub_cause_cd,
                   DECODE(prod_cd, '00', '', prod_nm) prod_nm,
                   DECODE(detail1_cause_cd, '9999', '총합계', '9998', '심의중', fail_nm) fail_nm,
                   DECODE(detail1_sub_cause_cd, '99', '합계', fail_sub_nm) fail_sub_nm,
                   ( date2009 + date2010 + date2011 ) sum_all,
                   date2009,
                   date2010,
                   date2011
            FROM   (SELECT NVL(prod_cd, '00') prod_cd,
                           NVL(detail1_cause_cd, '9999') detail1_cause_cd,
                           NVL(detail1_sub_cause_cd, '99') detail1_sub_cause_cd,
                           MAX(prod_nm) prod_nm,
                           MAX(fail_nm) fail_nm,
                           MAX(fail_sub_nm) fail_sub_nm,
                           SUM(date2009) date2009,
                           SUM(date2010) date2010,
                           SUM(date2011) date2011
                    FROM   (SELECT DECODE(process_cd, '1', prod_cd, '98') prod_cd,
                                   DECODE(process_cd, '1', detail1_cause_cd, '9998') detail1_cause_cd,
                                   DECODE(process_cd, '1', detail1_sub_cause_cd, '98') detail1_sub_cause_cd,
                                   prod_nm,
                                   fail_nm,
                                   fail_sub_nm,
                                   process_cd,
                                   DECODE(SUBSTR(account_dt, 0, 4), '2009', 1, 0) date2009,
                                   DECODE(SUBSTR(account_dt, 0, 4), '2010', 1, 0) date2010,
                                   DECODE(SUBSTR(account_dt, 0, 4), '2011', 1, 0) date2011
                            FROM   (SELECT ....,
                                           ROW_NUMBER() OVER (PARTITION BY B.delivery ORDER BY C.seq desc, A.account_dt DESC) as row_delivery_no
                                    FROM   (SELECT accept_no,
                                                   ...
                                            FROM   XX
                                            WHERE  1 = 1
                                            AND    ...
                                            --검색부분1
                                            AND    l_prod_cd IS NOT NULL
                                            AND    l_prod_det_cd IS NOT NULL ) A,
                                           YY B,
                                           ZZ C
                                    WHERE  A.run_model_f = B.matnr_f
                                    AND    A.product_code_f = B.prdno_f
                                    AND    A.prod_cd = decode(A.prod_det_cd, '0403', '04', B.l_prod_cd)
                                    AND    B.delivery = C.delivery_no (+)
                                    AND    TO_DATE(A.account_dt,'yyyymmdd') - TO_DATE(B.wadat,'yyyymmdd') > 0
                                    AND    90 >= TO_DATE(A.account_dt,'yyyymmdd') - TO_DATE(B.wadat,'yyyymmdd')
                                    --검색부분2
                                    )
                            WHERE  row_delivery_no = 1
                            AND    center_nm IS NOT NULL
                            AND    prod_nm IS NOT NULL )
                    GROUP BY ROLLUP(prod_cd, detail1_cause_cd, detail1_sub_cause_cd) )
            ORDER BY prod_cd, detail1_cause_cd, detail1_sub_cause_cd) a1
        WHERE 1 = 1
        AND (a1.prod_cd <> '98' OR a1.detail1_sub_cause_cd <> '99')
        AND (a1.fail_nm = '총합계' OR a1.fail_sub_nm <> '합계')