전체쿼립니다.ㅜ.ㅡ 참으로 허접하네요~ㅜ.ㅡ

흐흑~

SELECT  DECODE(A.JIKWE, '360', '350',                             /* 직위코드      */
                                       '380', '370',
                                     '395', '390',
                           A.JIKWE) AAJIKWE,
       GA05.COMCDNM,  

      DECODE(SUBSTR(B.RESNO,7,1),'1', '남')sex1 ,          /* 남자코드            */
    DECODE(SUBSTR(B.RESNO,7,1),'2', '녀')sex2,          /* 남자코드            */  

    COUNT(A.EMPNO) ,                                           /* 대상 인원수             */  
    SUM(A.TOTAMT),                                                  /* 총지급액                 */
    SUM(A.DEDTAMT),                                                /* 공제총액                 */
    SUM(A.REALAMT),                                                /* 실지급액                 */
    SUM(sCd1011),                                                    /* 기본급                    */
    SUM(sCd1012),                                                     /* 직급수당                 */
    SUM(sCd1013),                                                     /* 가족수당                  */
    SUM(sCd1014),                                                     /* 자격수당(1)              */
    SUM(sCd1015),                                                     /* 근속수당                   */
    SUM(sCd1016),                                                     /* 상여금                      */
    SUM(sCd1017),                                                     /* 체력수당                   */
    SUM(sCd1019),                                                     /* 특수부서수당              */
    SUM(sCd1020),                                                     /* 원무창구수당              */
    SUM(sCd1023),                                                     /* 시간외수당                 */
    SUM(sCd1024),                                                     /* 심야수당                    */
    SUM(sCd1025),                                                     /* 월차수당                    */
    SUM(sCd1027),                                                     /* 급식수당                    */
    SUM(sCd1028),                                                     /* 교통수당                    */
    SUM(sCd1030),                                                     /* 년차수당                    */
    SUM(sCd1031),                                                     /* 반장수당                    */
    SUM(sCd1032),                                                     /* 의무기록수당               */
    SUM(sCd1033),                                                     /* 마약관리수당               */
    SUM(sCd1034),                                                     /* 항암제조제수당            */
    SUM(sCd1035),                                                     /* 탕전예제수당               */
    SUM(sCd1036),                                                     /* 야간약국수당               */
    SUM(sCd1037),                                                     /* 조정수당                     */
    SUM(sCd1038),                                                     /* 파견수당                     */
    SUM(sCd1039),                                                     /* 기타제수당                   */
    SUM(sCd1040),                                                     /* 교육수당(친절교육강사비)    */
    SUM(sCd1041),                                                     /* 의무기록반장수당            */
    SUM(sCd1042),                                                     /* 업무추진비                  */
    SUM(sCd1043),                                                     /* 가정간호식대보조            */
    SUM(sCd1044),                                                     /* 방사선안전관리자수당        */
    SUM(sCd1045),                                                     /* 한방약제예제반장수당        */
    SUM(sCd1046),                                                     /* 당직비12                    */
    SUM(sCd1047),                                                     /* 당직비15                    */
    SUM(sCd1048),                                                     /* 당직비30                    */
    SUM(sCd1049),                                                     /* 당직비40                    */
    SUM(sCd1050),                                                     /* 응급콜수당                  */
    SUM(sCd1051),                                                     /* 교육수당(라마즈)            */
    SUM(sCd1052),                                                     /* 보직수당                    */
    SUM(sCd1053),                                                     /* 급여소급[승(진)급]          */
    SUM(sCd1054),                                                     /* 약사수당                    */
    SUM(sCd1055),                                                     /* 당직비                      */
    SUM(sCd1056),                                                     /* 장학금                      */
    SUM(sCd1057),                                                     /* 산전후 휴가급여             */
    SUM(sCd1060),                                                     /* 격려금                      */
    SUM(sCd1061),                                                     /* 인센티브(성과급)            */
    SUM(sCd1062),                                                     /* 특수업무수당1               */
    SUM(sCd1063),                                                     /* 특수업무수당2               */
    SUM(sCd1064),                                                     /* 연구수당                    */
    SUM(sCd1065),                                                     /* 자가운전비                  */
    SUM(sCd1066),                                                     /* 기본연봉                    */
    SUM(sCd1067),                                                     /* 능력연봉                    */
    SUM(sCd1068),                                                     /* 교육수당(Preceptor)         */
    SUM(sCd1069),                                                    /* 특근수당                    */
    SUM(sCd1097),                                                     /* 식대(95~96)과세             */
    SUM(sCd1098),                                                     /* 비과세급여                  */
    SUM(sCd1099),                                                     /* 기타수당                    */
    SUM(sCd1080)                                                       /* 자격수당(2)                 */
 
 
 
   
 FROM       GBMSALMT A,
  (SELECT EMPNO,
          SUM(DECODE(SDCD,'1011',SDAMT,0))sCd1011,
          SUM(DECODE(SDCD,'1012',SDAMT,0))sCd1012,
          SUM(DECODE(SDCD,'1013',SDAMT,0))sCd1013,
          SUM(DECODE(SDCD,'1014',SDAMT,0))sCd1014,
          SUM(DECODE(SDCD,'1015',SDAMT,0))sCd1015,
          SUM(DECODE(SDCD,'1016',SDAMT,0))sCd1016,
          SUM(DECODE(SDCD,'1017',SDAMT,0))sCd1017,
          SUM(DECODE(SDCD,'1019',SDAMT,0))sCd1019,
          SUM(DECODE(SDCD,'1020',SDAMT,0))sCd1020,
          SUM(DECODE(SDCD,'1023',SDAMT,0))sCd1023,
          SUM(DECODE(SDCD,'1024',SDAMT,0))sCd1024,
          SUM(DECODE(SDCD,'1025',SDAMT,0))sCd1025,
          SUM(DECODE(SDCD,'1027',SDAMT,0))sCd1027,
          SUM(DECODE(SDCD,'1028',SDAMT,0))sCd1028,
          SUM(DECODE(SDCD,'1030',SDAMT,0))sCd1030,
          SUM(DECODE(SDCD,'1031',SDAMT,0))sCd1031,
          SUM(DECODE(SDCD,'1032',SDAMT,0))sCd1032,
          SUM(DECODE(SDCD,'1033',SDAMT,0))sCd1033,
          SUM(DECODE(SDCD,'1034',SDAMT,0))sCd1034,
          SUM(DECODE(SDCD,'1035',SDAMT,0))sCd1035,
          SUM(DECODE(SDCD,'1036',SDAMT,0))sCd1036,
          SUM(DECODE(SDCD,'1037',SDAMT,0))sCd1037,
          SUM(DECODE(SDCD,'1038',SDAMT,0))sCd1038,
          SUM(DECODE(SDCD,'1039',SDAMT,0))sCd1039,
          SUM(DECODE(SDCD,'1040',SDAMT,0))sCd1040,
          SUM(DECODE(SDCD,'1041',SDAMT,0))sCd1041,
          SUM(DECODE(SDCD,'1042',SDAMT,0))sCd1042,
          SUM(DECODE(SDCD,'1043',SDAMT,0))sCd1043,
          SUM(DECODE(SDCD,'1044',SDAMT,0))sCd1044,
          SUM(DECODE(SDCD,'1045',SDAMT,0))sCd1045,
          SUM(DECODE(SDCD,'1046',SDAMT,0))sCd1046,
          SUM(DECODE(SDCD,'1047',SDAMT,0))sCd1047,
          SUM(DECODE(SDCD,'1048',SDAMT,0))sCd1048,
          SUM(DECODE(SDCD,'1049',SDAMT,0))sCd1049,
          SUM(DECODE(SDCD,'1050',SDAMT,0))sCd1050,
          SUM(DECODE(SDCD,'1051',SDAMT,0))sCd1051,
          SUM(DECODE(SDCD,'1052',SDAMT,0))sCd1052,
          SUM(DECODE(SDCD,'1053',SDAMT,0))sCd1053,
          SUM(DECODE(SDCD,'1054',SDAMT,0))sCd1054,
          SUM(DECODE(SDCD,'1055',SDAMT,0))sCd1055,
          SUM(DECODE(SDCD,'1056',SDAMT,0))sCd1056,
          SUM(DECODE(SDCD,'1057',SDAMT,0))sCd1057,
          SUM(DECODE(SDCD,'1060',SDAMT,0))sCd1060,
          SUM(DECODE(SDCD,'1061',SDAMT,0))sCd1061,
          SUM(DECODE(SDCD,'1062',SDAMT,0))sCd1062,
          SUM(DECODE(SDCD,'1063',SDAMT,0))sCd1063,
          SUM(DECODE(SDCD,'1064',SDAMT,0))sCd1064,
          SUM(DECODE(SDCD,'1065',SDAMT,0))sCd1065,
          SUM(DECODE(SDCD,'1066',SDAMT,0))sCd1066,
          SUM(DECODE(SDCD,'1067',SDAMT,0))sCd1067,
          SUM(DECODE(SDCD,'1068',SDAMT,0))sCd1068,
          SUM(DECODE(SDCD,'1069',SDAMT,0))sCd1069,
          SUM(DECODE(SDCD,'1097',SDAMT,0))sCd1097,
          SUM(DECODE(SDCD,'1098',SDAMT,0))sCd1098,
          SUM(DECODE(SDCD,'1099',SDAMT,0))sCd1099,
         SUM(DECODE(SDCD,'1080',SDAMT,0))sCd1080
    FROM GBMSDADT
     WHERE GIVGBN = &ssGivgbn
        AND GIVYYMM = &ssGivyymm
        AND LOCATE = &ssLocate
        AND PAYGBN = &ssPaygbn
    GROUP BY EMPNO) SDCD,
      GAINSAMT B,
   CCCOMCDT GA05,
   CCDEPTCT DEPT
 WHERE A.EMPNO  = SDCD.EMPNO
   AND A.EMPNO   = B.EMPNO
   AND A.LOCATE  = B.LOCATE
   AND A.GIVYYMM = &ssGivyymm
   AND A.GIVGBN  = &ssGivgbn
   AND DECODE(A.JIKWE, '360','350', /* 직위코드           */
                                      '380','370',
                                      '395','390',
                      A.JIKWE)  = GA05.COMCDDTL
   AND GA05.LARGCD = 'GA05'
   AND A.DEPTCD  = DEPT.DEPTCD
   AND A.LOCATE  = DEPT.LOCATE
      AND DEPT.ACCLOC = DECODE(&ssDeptcd,'', DEPT.ACCLOC,&ssDeptcd)

   GROUP BY DECODE(A.JIKWE, '360','350',         /*  직위코드  */
                                                 '380','370',
                                                 '395','390',
                                 A.JIKWE),
             GA05.COMCDNM,   
             DECODE(SUBSTR(B.RESNO,7,1),'1', '남'),
             DECODE(SUBSTR(B.RESNO,7,1),'2', '녀')
   ORDER BY AAJIKWE, SEX1, SEX2