ORA-01467 sort key too long ????

From: ±¸º»À¯ <bykoo_at_kisdj.co.kr>
Date: Mon, 18 Oct 1999 13:56:37 +0900
Message-ID: <7uebsi$o4b$2@news2.kornet.net>
Please!
--Oracle 8
--sql(Tota 253 line)
SELECT  D.BS_NM                     BS_NM,
        D.PS_NM                     PS_NM,
        D.PS_REG_NO                 PS_REG_NO,
        D.PS_JUMIN_NO               PS_JUMIN_NO,
        D.PS_ADDR                   PS_ADDR,--Â¡¼öÀÇ¹«ÀÚ »çÇ×
        E.NAME                      PAY_NM,
        E.JUMIN_NO                  PAY_JUMIN_NO,
        E.ADDR                      PAY_ADDR,   --¼ÒµæÀÚ»çÇ×
        C.MEMBER_NO,


        '01'  MON1,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'01',PAY_AMT,0)) PAY_TOT_AMT1,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'01',PAY_AMT * 0.75,0))
                                    PILYO_AMT1, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'01',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT1, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT1,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'01',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT1,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'01',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT1,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'01',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT1,
        --------------------------------------------------------------------
-----------1¿ù


        '02'  MON2,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'02',PAY_AMT,0)) PAY_TOT_AMT2,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'02',PAY_AMT * 0.75,0))
                                    PILYO_AMT2, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'02',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT2, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT2,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'02',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT2,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'02',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT2,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'02',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT2,
        --------------------------------------------------------------------
-----------2¿ù


        '03'  MON3,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'03',PAY_AMT,0))
                                    PAY_TOT_AMT3,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'03',PAY_AMT * 0.75,0))
                                    PILYO_AMT3, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'03',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT3, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT3,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'03',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT3,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'03',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT3,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'03',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT3,
        --------------------------------------------------------------------
-----------3¿ù


        '04'  MON4,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'04',PAY_AMT,0))
                                    PAY_TOT_AMT4,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'04',PAY_AMT * 0.75,0))
                                    PILYO_AMT4, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'04',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT4, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT4,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'04',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT4,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'04',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT4,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'04',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT4,
        --------------------------------------------------------------------
-----------4¿ù


        '05'  MON5,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'05',PAY_AMT,0))
                                    PAY_TOT_AMT5,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'05',PAY_AMT * 0.75,0))
                                    PILYO_AMT5, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'05',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT5, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT5,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'05',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT5,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'05',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT5,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'05',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT5,
        --------------------------------------------------------------------
-----------5¿ù


        '06'  MON6,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'06',PAY_AMT,0))
                                    PAY_TOT_AMT6,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'06',PAY_AMT * 0.75,0))
                                    PILYO_AMT6, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'06',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT6, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT6,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'06',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT6,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'06',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT6,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'06',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT6,
        --------------------------------------------------------------------
-----------6¿ù


        '07'  MON7,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'07',PAY_AMT,0))
                                    PAY_TOT_AMT7,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'07',PAY_AMT * 0.75,0))
                                    PILYO_AMT7, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'07',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT7, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT7,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'07',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT7,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'07',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT7,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'07',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT7,
        --------------------------------------------------------------------
-----------7¿ù


        '08'  MON8,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'08',PAY_AMT,0))
                                    PAY_TOT_AMT8,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'08',PAY_AMT * 0.75,0))
                                    PILYO_AMT8, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'08',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT8, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT8,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'08',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT8,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'08',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT8,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'08',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT8,
        --------------------------------------------------------------------
-----------8¿ù


        '09'  MON9,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'09',PAY_AMT,0))
                                    PAY_TOT_AMT9,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'09',PAY_AMT * 0.75,0))
                                    PILYO_AMT9, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'09',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT9, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT9,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'09',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT9,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'09',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT9,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'09',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT9,
        --------------------------------------------------------------------
-----------9¿ù


        '10'  MON10,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'10',PAY_AMT,0))
                                    PAY_TOT_AMT10,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'10',PAY_AMT * 0.75,0))
                                    PILYO_AMT10, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'10',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT10, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT10,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'10',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT10,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'10',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT10,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'10',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT10,
        --------------------------------------------------------------------
-----------10¿ù


        '11'  MON11,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'11',PAY_AMT,0))
                                    PAY_TOT_AMT11,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'11',PAY_AMT * 0.75,0))
                                    PILYO_AMT11, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'11',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT11, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT11,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'11',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT11,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'11',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT11,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'11',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT11,
        --------------------------------------------------------------------
-----------11¿ù


        '12'  MON12,


        sum(DECODE(TO_CHAR(B.PAY_DT,'MM'),'12',PAY_AMT,0))
                                    PAY_TOT_AMT12,
        SUM(DECODE(TO_CHAR(B.PAY_DT,'MM'),'12',PAY_AMT * 0.75,0))
                                    PILYO_AMT12, --ÇÊ¿ä°æºñ
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'12',TRUNC(SUM(PAY_AMT - (PAY_AMT *
0.75))),0)
                                    SODEK_AMT12, --¼Òµæ±Ý¾×


        '20%'                       SEYUL_PCT12,--¼¼À²


        DECODE(TO_CHAR(B.PAY_DT,'MM'),'12',TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)),0)
                                    SODEK_TAX_AMT12,--°©±Ù¼¼(¼Òµæ¼¼)
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'12',TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1),0)
                                    JUMIN_TAX_AMT12,--ÁÖ¹Î¼¼
        DECODE(TO_CHAR(B.PAY_DT,'MM'),'12',(
                                            TRUNC(SUM((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) +
                                            TRUNC(SUM(((PAY_AMT - (PAY_AMT *
0.75)) * 0.2)) * 0.1)
                                           ),0)
                                    GONGJE_TOT_AMT12
       FROM V_RE_PROJECT_ALL   A,
            RE_PROJ_PAY        B,
            RE_PROJAMT_MEMBER  C,
            (SELECT X.BUSINESS_CO_NM   BS_NM,
                    X.REPRESENT_PS_NM  PS_NM,
                    X.BUSINESS_REG_NO  PS_REG_NO,
                    Y.PERSON_NO        PS_JUMIN_NO,
                    Y.ADDR             PS_ADDR
               FROM CM_BUSINESS_CORP X,
                    V_CMPM_INSA Y
              WHERE X.BUSINESS_CD = '0000000001'
                AND X.REPRESENT_PS_NM = Y.KNAME(+)
             )  D,
            V_CM_PERSON_ALL    E
      WHERE A.PROJECT_NO = B.PROJECT_NO
        AND B.PROJECT_NO = C.PROJECT_NO
        AND B.EXPENSE_CD = C.EXPENSE_CD
        and B.PAY_DT     = C.PAY_DT
        AND C.MEMBER_NO  = E.MEMBER_NO(+)
        AND TO_CHAR(C.PAY_DT,'YYYY') ='1999'
        AND C.MEMBER_NO = '10030'
GROUP BY C.MEMBER_NO        ,
         TO_CHAR(B.PAY_DT,'MM'),
        D.BS_NM,
        D.PS_NM,
        D.PS_REG_NO,
        D.PS_JUMIN_NO,
        D.PS_ADDR,
        E.NAME,
        E.JUMIN_NO,
        E.ADDR
Received on Sun Oct 17 1999 - 23:56:37 CDT