Re: Beating the 255 variable DECODE fn - 8.1.6

decided to stubbonly plod on with the decode i see. :-)

steve

<josn_at_my-deja.com> wrote:

> 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 - 19:44:17 CST