Re: User-defined SQL Function

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 declare
c varchar2(200);
r number;
i number;
begin
c:=NULL;
for i in 1..20 loop
if trunc(mod(bitnumber,2**i)/(2**(i-1)))=1 then c:=c||'c'||to_char(i)||'+';
end if;
end loop
c:='select '||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;

HTH 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 Tue Jul 11 2000 - 00:00:00 CEST