SELECT TMP.PATHCD AS PATHCD ,(SELECT CLAS.CLSNM AS CLSSUPNM FROM AST.AYAMCLAS CLAS WHERE CLAS.CLSCD=TMP.ORDCD ) AS CLSSUPNM , CASE TMP.PATHCD WHEN 'C0004' THEN TMP.CLSNM||'(세례/유아세례/보례)' WHEN 'C0006' THEN TMP.CLSNM||'(성사/관면혼)' ELSE (CASE TMP.CLSSUPCD WHEN 'C0002' THEN SUBSTR(TMP.CLSNM,1,6) ELSE TMP.CLSNM END) END AS CLSNM , CASE D.CLSCD WHEN 'C0009' THEN COUNT(D.ACTDD) ELSE SUM(CASE C.DIVSVCTRGTMANPOSCD WHEN 'DT1' THEN 1 ELSE 0 END) END AS DT1 , SUM(CASE C.DIVSVCTRGTMANPOSCD WHEN 'DT2' THEN 1 ELSE 0 END)AS DT2 , SUM(CASE C.DIVSVCTRGTMANPOSCD WHEN 'DT3' THEN 1 ELSE 0 END)AS DT3 , SUM(CASE C.DIVSVCTRGTMANPOSCD WHEN 'DT4' THEN 1 WHEN 'DT5' THEN 1 WHEN 'DT6' THEN 1 ELSE 0 END)AS DT4 , (SUM(CASE C.DIVSVCTRGTMANPOSCD WHEN 'DT1' THEN 1 ELSE 0 END)+ SUM(CASE C.DIVSVCTRGTMANPOSCD WHEN 'DT2' THEN 1 ELSE 0 END)+ SUM(CASE C.DIVSVCTRGTMANPOSCD WHEN 'DT3' THEN 1 ELSE 0 END)+ SUM(CASE C.DIVSVCTRGTMANPOSCD WHEN 'DT4' THEN 1 WHEN 'DT5' THEN 1 WHEN 'DT6' THEN 1 ELSE 0 END)) AS TOTAL FROM ( SELECT LEVEL AS LEVL , CLAS.CLSCD AS CLSCD , DECODE(LEVEL,2,DECODE(CLSCD,'C0025',CLSNM,SUBSTR(SYS_CONNECT_BY_PATH(CLAS.CLSNM,'*'),2,4)),CLSNM) AS CLSNM , SUBSTR(SYS_CONNECT_BY_PATH(CLAS.CLSSUPCD,'*'),2,5) AS CLSSUPCD , DECODE(CLSCD,'C0025',CLSSUPCD||'.'||CLSCD,SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(CLSCD),'*'),2,5)) AS PATHCD , SUBSTR(SYS_CONNECT_BY_PATH(CLAS.CLSSUPCD,'*'),2,5) AS ORDCD FROM AST.AYAMCLAS CLAS START WITH CLAS.SCRNCD IN('C1','C2','C3','XX') AND CLAS.CLSFLAG='C' AND CLAS.CLSLEVL ='3' AND CLAS.CLSCD!='C0020' AND CLAS.INSTCD ='{?instcd}' AND CLAS.DELDD = '00000000' CONNECT BY NOCYCLE PRIOR CLSCD=CLSSUPCD )TMP LEFT OUTER JOIN (SELECT DITG.CLSCD AS CLSCD , DITG.DIVSVCTRGTMANPOSCD AS DIVSVCTRGTMANPOSCD , EFDM.FROMDD AS FROMDD FROM AST.AYADDITG DITG LEFT OUTER JOIN AST.AYAHEFDM EFDM ON ( DITG.SEQNO = EFDM.SEQNO AND DITG.CLSCD = EFDM.CLSCD AND DITG.INSTCD='{?instcd}' AND DITG.INSTCD = EFDM.INSTCD AND DITG.DELDD ='00000000' AND EFDM.DELDD ='00000000' ) WHERE EFDM.FROMDD BETWEEN '{?fromdd}'||'01' AND '{?todd}'||'31' )C ON(C.CLSCD=TMP.CLSCD) LEFT OUTER JOIN (SELECT CLAS.CLSCD AS CLSCD , CLAS.INSTCD AS INSTCD , PATM.ACTDD AS ACTDD FROM AST.AYAHPATM PATM INNER JOIN AST.AYAMCLAS CLAS ON ( PATM.ACTFLAG='3' AND PATM.INSTCD='{?instcd}' AND PATM.INSTCD =PATM.INSTCD AND CLAS.CLSCD='C0009' AND CLAS.DELDD='00000000' AND PATM.DELDD='00000000' ) WHERE PATM.ACTDD BETWEEN '{?fromdd}'||'01' AND '{?todd}'||'31' )D ON(D.CLSCD=TMP.CLSCD) GROUP BY TMP.PATHCD , TMP.ORDCD , TMP.CLSNM , D.CLSCD , TMP.CLSSUPCD ORDER BY TMP.PATHCD