| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Beating the 255 variable DECODE fn - 8.1.6
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
<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/
Received on Wed Jan 17 2001 - 00:26:49 CST
 |
 |