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
Just to clarify the need here:
I want to have the data in a view - so that it remains current for
reporting purposes. I could write a stored procedure which will do the
job but that will dump the data in a table. However to
keep the data current that procedure will need to executed every time a
user requests a view [report].
I hope this sheds more light on the problem, -jos
In article <tQH86.6055$J%.609071_at_news.flash.net>,
"Eric Givler" <egivler_at_flash.net> wrote:
> I like your second suggestion to create another table. I can't see
any
> reason at all to have a decode for that many items. It just spells
out: PUT
> ME IN A TABLE!
>
> "Spencer" <spencerp_at_swbell.net> wrote in message
> news:An386.424$hD5.13350_at_nnrp1.sbc.net...
> > a simple workaround to the limit on the number of arguments
> > in a decode statement is to include another decode as the
> > default expression, e.g.
> >
> > decode(myexpr,val1,ret1,val2,ret2,decode
(myexpr,val3,ret3,val4,ret4,NULL))
> >
> > if you are doing this in PL/SQL, then it might be easier to maintain
> > two PL/SQL tables, one for the list arguments and one for the list
> > of associated return values, and write code to perform a lookup.
> >
> > if you need to do this in SQL, then another alternative to consider
> > would be to create a "lookup" table with the two columns (one with
> > the argument, one with the return value), and perform a join.
> >
> > HTH
> >
> > <josn_at_my-deja.com> wrote in message
news:93nfmb$nfo$1_at_nnrp1.deja.com...
> > > 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/
> >
> >
>
>
Sent via Deja.com
http://www.deja.com/
Received on Mon Jan 15 2001 - 13:31:49 CST