Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Beating the 255 variable DECODE fn - 8.1.6

Re: Beating the 255 variable DECODE fn - 8.1.6

From: Spencer <spencerp_at_swbell.net>
Date: Mon, 15 Jan 2001 19:51:30 -0600
Message-ID: <6MN86.1783$hD5.43179@nnrp1.sbc.net>

<josn_at_my-deja.com> wrote in message news:93vipu$u9f$1_at_nnrp1.deja.com...
> Ok let me restate the problem here:
>
> I have data stored in a table in the format I have described below
>
> Name Value
> -----------------------
> IssueID 1000
> Assigned to Fred
> .....
> IssueID 1001
> Assigned to Joe
> .....

ok, so now i am at a total loss as to an understanding of your description of the table definition (what exactly are the column names, column datatypes, and constraints ?) from your example, it appears that your table consists of two columns, one named "Name", the other named "Value". Given this design, what criteria do you plan to use match "IssueID" rows with "Assigned to" rows ?

> The Name/Value attibutes per IssueID are about 36 and what I want to
> achieve is a new dataset in a view that looks like:
>
> IssueID Assigned to
> 1000 Fred
> 1001 Joe
>
> such that the view will have 36 columns. Now if I use the DECODE
> funtion, I end up with over 255 parameters all in all using the format:
> max(decode(expr,compare1',compare2,null))return_value. Any ideas?
>

ok, so now i am even more confused. you show two columns in the sample resultset from your view, yet you specify that the view will have 36 columns... how many columns do you actually plan for your view definition to contain ? for what reason do you need to include the aggregate function MAX ?

going from "about 36" attribute pairs to an exact column count of 36 in your view definition appears to be a bizarrely contorted design.

what problem are you actually trying to solve ? what information, exactly, is the view definition supposed to provide ?

it appears that your problem space includes two entities: (1) "issue" and (2) "individual", and a relationship, named "assigned to", between the two entities.

you need answers to a couple of basic questions about the relationship(s) between the entities, like how many "issues" can be "assigned to" an "individual"? (zero, one or more ?) how many "individuals" can be "assigned to" an "issue"? (zero, one, or more?)

it appears that "assigned to" may be a classic "many-to-many" relationship between the "issue" and "individual" entities. if so, the relationship can be easily implemented as a table comprised of the primary keys of the two entity tables.

e.g.

create table myissue
(ISSUEID varchar2(2000) not null
,constraint myissue_pk primary key (ISSUEID)) ;
create table myindividuals
(INDIVID varchar2(2000) not null
,constraint myindividuals_pk primary key (INDIVID)) ;
create table myassignedto
(ISSUEID varchar2(2000) not null
,INDIVID varchar2(2000) not null
,constraint myassignedto_pk primary key (ISSUEID,INDIVID)) ;

add a couple of foreign key constraints, and you've got a reasonable approximation of a normalized design. either that, or i am totally confused as to what problem you are attempting to solve. suffice it to say, the DECODE function is not a replacement for database design.

good luck.

> =Jos
>
> In article <93rfki$ppk$1_at_nnrp1.deja.com>,
> Mike Krolewski <mkrolewski_at_rii.com> wrote:
> > In article <93nfmb$nfo$1_at_nnrp1.deja.com>,
> > josn_at_my-deja.com wrote:
> > > Hello,
> > > I am a newbie in PL/SQL and one task I am trying to accomplish
> > > is "transpose" data in a table to a view for reporting purposes. The
> > > data is stored in the format
> > >
> > > Name Value
> > > -----------------------
> > > IssueID 1000
> > > Assigned to Fred
> > > .....
> > > IssueID 1001
> > > .....
> > >
> > > I have successfully managed to use DECODE to transpose it but I am
> > > running into the 255 variable limit. Does anyone have a possible
> > > workaround to this apart from splitting the DECODE into 2 views then
> > > creating a third view to join them?
> > >
> > > Thanks,
> > > -Jos
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> > >
> >
> > You might want to restate the original problem and what you mean by
> > 'transpose data'. It sounds like a problem that could be solved by a
> > different means. 255 decode statements is quite unusual.
> >
> > Again, state what the problem is, and your solution.
> >
> > --
> > Michael Krolewski
> > Rosetta Inpharmatics
> > mkrolewski_at_rii.com
> > Usual disclaimers
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
>
Received on Mon Jan 15 2001 - 19:51:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US