Re: Beating the 255 variable DECODE fn - 8.1.6

From: <josn_at_my-deja.com>
Date: Tue, 16 Jan 2001 03:05:04 GMT
Message-ID: <940dos$m4n$1@nnrp1.deja.com>

I have figured out where I was going wrong with the decode function but I am now running into "ORA-01467 sort key too long" error message. I have basically gone ahead and created a view using the code below. Do you have any idea on how to resolve this error without splitting up the view - which seems to be the only proposed solution in the server error messages?

select colname1,

max(decode(name,'compare1',value,null))compare1,
max(decode(name,'compare2',value,null))compare2,
max(decode(name,'compare3',value,null)) compare3,
max(decode(name,'compare4',value,null))compare4,
max(decode(name,'compare5'value,null))compare5,

.
.

<snip>
.
.

max(decode(name,'compare36',value,null))compare36,

from <tablename1> tab1
where (select <colname2>
from <tablename2> tab2
where tab2.colname2=tab1.colname1)
in(select distinct <colname3> from <tablename3> ) group by <colname1>

Thanks folks - this forum is a real eye opener, -Jos

Sent via Deja.com
http://www.deja.com/ Received on Mon Jan 15 2001 - 21:05:04 CST