Re: Beating the 255 variable DECODE fn - 8.1.6

Some feedback on using DECODE function:

I seem to have successfully solved the "ORA-01467 Sort key too long" error by using the DECODE funtion in the following format:

select colname1,
max(distinct decode(name,'compare1',value,null))compare1, max(distinct decode(name,'compare2',value,null))compare2, etc
max(distinct decode(name,'compare36',value,null))compare36, from <tablename...>
group by colname1

This way, I have all my 36 new columns [compare1...comapre36] in the view and returning the correct data

-Jos

In article <YTa96.3571$hD5.64271_at_nnrp1.sbc.net>,   "Spencer" <spencerp_at_swbell.net> wrote:
> this query should return the result set:
>
> SELECT name1.ID As ID
> , name1.Value As Name1
> , name2.Value As Name2
> , name3.Value As Name3
> FROM (SELECT ID, Value FROM <tablename>
> WHERE Name = 'Name1') name1
> , (SELECT ID, Value FROM <tablename>
> WHERE Name = 'Name2') name2
> , (SELECT ID, Value FROM <tablename>
> WHERE Name = 'Name3') name3
> , (SELECT ID, Value FROM <tablename>
> WHERE Name = 'Name4') name4
> WHERE name1.ID = name2.ID
> AND name1.ID = name3.ID
> AND name1.ID = name4.ID
>
> <josn_at_my-deja.com> wrote in message

 news:94262c$5db$1_at_nnrp1.deja.com...
> > ok, let me and try to get this straight. I think I have been all
 over
> > the place and that hasn't helped - apologies.
> >
> > My table looks like this:
> >
> > <tablename>
> > ID Numeric NOT NULL
> > Name VARCHAR2(30) NOT NULL
> > Value VARCHAR(256) NOT NULL
> >
> > The data is stored as follows:
> > ID NAME VALUE
> > -------------------------------
> > 1001 Name1 3
> > 1001 Name2 1
> > 1001 Name3 Joe
> > 1001 Name4 Q123E
> > 1002 Name1 2
> > 1002 Name2 10
> > 1002 Name3 Fred
> > 1002 Name4 W45TL
> >
> > etc
> >
> > What I want to achieve is a view that looks like:
> >
> > ID Name1 Name2 Name3 Name4
> > -------------------------------------
> > 1001 3 1 Joe Q123E
> > 1002 2 10 Fred W45TL
> >
> > First I need a view as opposed toa table so that data stays current
 for
> > reporting requirements. Initially it seemed to me that the DECODE
> > funtion was the way to solve this. I agree my initial assesment of
> > beating the 255 parameter limit was totally off course. However,
 after
> > redoing the query (see message 8) in this thread, I am coming up
 with
> > the "ORA-01467 sort key too long" error. Whether DECODE is the
 correct
> > way to solve the problem or whether there is a better way is not
 clear
> > to me as I am just diving into Oracle as a whole.
> >
> > Any help is highly appreciated and I hope that this states the
 problem
> > and solution I am looking for more concisely
> >
> > -Jos
> >
> >
> >
> > In article <1enbxjp.yc32772kx306N%E_at_E.COM>,
> > E_at_E.COM (steve) wrote:
> > > 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/
> > >
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 17 2001 - 12:27:51 CST