Re: ORA-01467 sort key too long ????

From: <michael_bialik_at_my-deja.com>
Date: Tue, 19 Oct 1999 20:40:24 GMT
Message-ID: <7uikvm$svg$1@nnrp1.deja.com>

Hi.

 The total length of fields used for sort exceeds internal  Oracle buffer size ( Sort is used because of GROUP BY clause ):

     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

  In my opinion the best thing to do is to rewrite the query in   following way :

1. Remove all references to the fields of table V_CM_PERSON_ALL from your query.
2. If the new query works - OK, otherwise - remove all references to table D as well.
3. Write ( if select after first paragraph is working ) : SELECT XX.*, E.NAME, E.JUMIN_NO, E.ADDR FROM ( <New_select> ) XX, V_CM_PERSON_ALL E WHERE XX.MEMBER_NO = E.MEMBER_NO(+)

  I don't quite understand : How many rows are retrieved from   table D ( in-line view )?
  If only one - remove it first.

  If you are using that select inside PL/SQL code - another solution is   to remove references to table E and to make a SELECT ... FROM E   inside CURSOR LOOP.

  HTH. Michael.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 19 1999 - 15:40:24 CDT