Re: Beating the 255 variable DECODE fn - 8.1.6

Hi,
what you need to do is the following:

1.explain the table structure clearly.
2.give an example of the data in the table.
3.show what you want. ( without using sample code)
4. post to this group.

E.G.
1.
mytable
name varchar2(20)
value number(5)

2.david,50

etc
from your post, you are asking help to fix a problem ( secondary problem)to what appears to be a misuse of the decode function, if you do the above we will be able to offer solutions to your base problem, possibly a new aproach.

steve

<josn_at_my-deja.com> wrote:

> 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 - 23:42:12 CST