Re: Beating the 255 variable DECODE fn - 8.1.6

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/ Received on Tue Jan 16 2001 - 13:05:57 CST