ORA-01467 sort key too long.
Hi Team ,
While I am excueting the below query ,its working on oracle 10g but its not working on oracle 9i .
Its throwing an error *"ORA-01467 sort key too long."*
Query
SELECT ARZ_LOC_ID,
MAX(DECODE(R,1,ARZ_DOC_THPY1)) ARZ_DOC_THPY1
*,MAX(DECODE(R,2,ARZ_DOC_THPY1)) ARZ_DOC_THPY2*
*,MAX(DECODE(R,3,ARZ_DOC_THPY1)) ARZ_DOC_THPY3*
*,MAX(DECODE(R,4,ARZ_DOC_THPY1)) ARZ_DOC_THPY4*
*,MAX(DECODE(R,5,ARZ_DOC_THPY1)) ARZ_DOC_THPY5*
*,MAX(DECODE(R,6,ARZ_DOC_THPY1)) ARZ_DOC_THPY6*
*,MAX(DECODE(R,7,ARZ_DOC_THPY1)) ARZ_DOC_THPY7*
*,MAX(DECODE(R,8,ARZ_DOC_THPY1)) ARZ_DOC_THPY8*
*,MAX(DECODE(R,9,ARZ_DOC_THPY1)) ARZ_DOC_THPY9*
*,MAX(DECODE(R,10,ARZ_DOC_THPY1)) ARZ_DOC_THPY10*
*,MAX(DECODE(R,11,ARZ_DOC_THPY1)) ARZ_DOC_THPY11*
*,MAX(DECODE(R,12,ARZ_DOC_THPY1)) ARZ_DOC_THPY12*
*,MAX(DECODE(R,13,ARZ_DOC_THPY1)) ARZ_DOC_THPY13*
*,MAX(DECODE(R,14,ARZ_DOC_THPY1)) ARZ_DOC_THPY14*
*,MAX(DECODE(R,15,ARZ_DOC_THPY1)) ARZ_DOC_THPY15*
*,MAX(DECODE(R,16,ARZ_DOC_THPY1)) ARZ_DOC_THPY16*
*,MAX(DECODE(R,17,ARZ_DOC_THPY1)) ARZ_DOC_THPY17*
*,MAX(DECODE(R,18,ARZ_DOC_THPY1)) ARZ_DOC_THPY18*
*,MAX(DECODE(R,19,ARZ_DOC_THPY1)) ARZ_DOC_THPY19*
*,MAX(DECODE(R,20,ARZ_DOC_THPY1)) ARZ_DOC_THPY20*
*,MAX(DECODE(R,21,ARZ_DOC_THPY1)) ARZ_DOC_THPY21*
*,MAX(DECODE(R,22,ARZ_DOC_THPY1)) ARZ_DOC_THPY22*
*,MAX(DECODE(R,23,ARZ_DOC_THPY1)) ARZ_DOC_THPY23*
*,MAX(DECODE(R,24,ARZ_DOC_THPY1)) ARZ_DOC_THPY24*
*,MAX(DECODE(R,25,ARZ_DOC_THPY1)) ARZ_DOC_THPY25*
*,MAX(DECODE(R,1,NANO)) NANO_1*
*,MAX(DECODE(R,2,NANO)) NANO_2*
*,MAX(DECODE(R,3,NANO)) NANO_3*
*,MAX(DECODE(R,4,NANO)) NANO_4*
*,MAX(DECODE(R,5,NANO)) NANO_5*
*,MAX(DECODE(R,6,NANO)) NANO_6*
*,MAX(DECODE(R,7,NANO)) NANO_7*
*,MAX(DECODE(R,8,NANO)) NANO_8*
*,MAX(DECODE(R,9,NANO)) NANO_9*
*,MAX(DECODE(R,10,NANO)) NANO_10*
*,MAX(DECODE(R,11,NANO)) NANO_11*
*,MAX(DECODE(R,12,NANO)) NANO_12*
*,MAX(DECODE(R,13,NANO)) NANO_13*
*,MAX(DECODE(R,14,NANO)) NANO_14*
*,MAX(DECODE(R,15,NANO)) NANO_15*
*,MAX(DECODE(R,16,NANO)) NANO_16*
*,MAX(DECODE(R,17,NANO)) NANO_17*
*,MAX(DECODE(R,18,NANO)) NANO_18*
*,MAX(DECODE(R,19,NANO)) NANO_19*
*,MAX(DECODE(R,20,NANO)) NANO_20*
*,MAX(DECODE(R,21,NANO)) NANO_21*
*,MAX(DECODE(R,22,NANO)) NANO_22*
*,MAX(DECODE(R,23,NANO)) NANO_23*
*,MAX(DECODE(R,24,NANO)) NANO_24*
*,MAX(DECODE(R,25,NANO)) NANO_25*
*,MAX(DECODE(R,1,DESCR)) DESCR_1*
*,MAX(DECODE(R,2,DESCR)) DESCR_2*
*,MAX(DECODE(R,3,DESCR)) DESCR_3*
*,MAX(DECODE(R,4,DESCR)) DESCR_4*
*,MAX(DECODE(R,5,DESCR)) DESCR_5*
*,MAX(DECODE(R,6,DESCR)) DESCR_6*
*,MAX(DECODE(R,7,DESCR)) DESCR_7*
*,MAX(DECODE(R,8,DESCR)) DESCR_8*
*,MAX(DECODE(R,9,DESCR)) DESCR_9*
*,MAX(DECODE(R,10,DESCR)) DESCR_10*
*,MAX(DECODE(R,11,DESCR)) DESCR_11*
*,MAX(DECODE(R,12,DESCR)) DESCR_12*
*,MAX(DECODE(R,13,DESCR)) DESCR_13*
*,MAX(DECODE(R,14,DESCR)) DESCR_14*
*,MAX(DECODE(R,15,DESCR)) DESCR_15*
*,MAX(DECODE(R,16,DESCR)) DESCR_16*
*,MAX(DECODE(R,17,DESCR)) DESCR_17*
*,MAX(DECODE(R,18,DESCR)) DESCR_18*
*,MAX(DECODE(R,19,DESCR)) DESCR_19*
*,MAX(DECODE(R,20,DESCR)) DESCR_20*
*,MAX(DECODE(R,21,DESCR)) DESCR_21*
*,MAX(DECODE(R,22,DESCR)) DESCR_22*
*,MAX(DECODE(R,23,DESCR)) DESCR_23*
*,MAX(DECODE(R,24,DESCR)) DESCR_24*
*,MAX(DECODE(R,25,DESCR)) DESCR_25*
FROM (SELECT ARZ_LOC_ID,ARZ_DOC_THPY1,PRIORITY_LEVEL,
ROW_NUMBER() OVER(PARTITION BY ARZ_LOC_ID ORDER BY 1) R,
GEO_ID,POSTCODE,STREET,BSNR, nano,
DESCR FROM (
SELECT ARZ_LOC_ID,ARZ_DOC_THPY1,PRIORITY_LEVEL,R,
GEO_ID,POSTCODE,STREET,BSNR, nano,
dense_rank() over(PARTITION BY arz_loc_id ORDER BY nano) tt
*, lead(nano) over(PARTITION BY arz_loc_id ORDER BY nano, priority_level) tt_1*
*, DESCR*
FROM (
SELECT
ARZ_LOC_ID,ARZ_DOC_THPY1,PRIORITY_LEVEL,R,
GEO_ID,POSTCODE,STREET,BSNR
*, SUBSTR(NANOSEGMENT,1,INSTR(NANOSEGMENT,';',1)-1) nano*
*, SUBSTR(NANOSEGMENT,INSTR(NANOSEGMENT,';',1,2)+1) DESCR*
FROM TMP_ARZ_VALID_DATA
*)*
*) WHERE (nano!=tt_1 OR tt_1 IS NULL ) AND nano IS NOT NULL*
*) GROUP BY ARZ_LOC_ID*
Thanks in Advace .
Regards,
Augustine