Re: User-defined SQL Function

That's really pretty good, but ultimately, I was hoping for a way to do it without passing the par_pr_key in and doing another lookup in the function. It just seemed like it would be possible if there was some way to operate on the current row in the function.

-Dave

"Alexander Penev" <alex_at_cska.net> wrote in message news:396BA2E2.CD5EEF6B_at_cska.net...
> Something like that (i haven't checked it for syntax error but it should
 help
> you i hope) :
>
> create function func (bitnumber number , par_pr_key ...) return number is
> declare
> c varchar2(200);
> r number;
> i number;
> begin
> c:='select ';
> for i in 1..20 loop
> if trunc(mod(bitnumber,power(2,i))/(power(2,(i-1))))=1 then
> c:=c||'c'||to_char(i)||'+';
> end if;
> end loop;
> c:=c||'0 into r from yourtable where pr_key= par_pr_key';
> EXECUTE IMMEDIATE c; --(or dbms_sql if not 8i)
> return r;
> end func;
>
>
>
>
> David Zuzga wrote:
>
> > Does anyone have an idea how to implement the following?
> >
> > I have a table t1 with 20 columns, say c1-c20. I would like to write a
> > function that I can call like this:
> >
> > select func(99999) from t1;
> >
> > The 99999 will be replaced with a bit-representation of the columns that
 I
> > wish to add together. For example, if I execute func(5), it will return
> > c3+c1, func(7) will return c3+c2+c1, func(15) will return c4+c3+c2+c1.
> >
> > I can't think of a straightforward way of doing this. Any help will be
> > greatly appreciated.
> >
> > Thanks,
> >
> > Dave Zuzga
> > dzuzga_at_isdinc.com
>
Received on Wed Jul 12 2000 - 00:00:00 CEST