Thanks for that solution. I am trying it out.
-Murali
Anurag Varma wrote:
> "moose1729" <murali.lakshman_at_gmail.com> wrote in message
news:1104207618.313457.136750_at_z14g2000cwz.googlegroups.com...
> >
> > Anurag Varma wrote:
> > > <murali.lakshman_at_gmail.com> wrote in message
> > news:1104080612.417167.244200_at_z14g2000cwz.googlegroups.com...
> > > > I have a problem where I want to reset cumulative percentage on
a
> > table
> > > > if it becomes greater than 1. I have solved this problem for
now
> > using
> > > > collections but the solution is not very good in terms of
> > performance.
> > > > I would really prefer the solution to be more in SQL than
pl/sql to
> > > > help performance. Having said that, I think it is impossible
to do
> > > > this in one sql statement unless you use "MODEL BY" clause in
10g.
> > I
> > > > am currently using 9i.
> > > >
> > > > I know there can be clever solutions created using "Pipelined"
> > > > functions and I want to see if some folks have ideas on this.
> > > >
> > > --snip--
> > >
> > > So did you try a solution using pipelined functions? What have
you
> > come up withso far?
> > > I see this being a relatively simple pl/sql problem if you try
doing
> > it using pipelined functions...
> > >
> > > or are you looking for SQL solutions without using pipelined
> > functions?
> > >
> > > Anurag
> >
> > I have come up with a solution in pl/sql (not using pipelined
> > functions) using just collections. But we have to run 8 million
rows
> > against this process and doing the way I have done takes too much
time.
> >
> > I want to try an approach using pipelined functions. If you can
throw
> > some ideas around how I can go about that would be a big help.
> > Thanks for your time
> >
>
> Not sure about its performance...
> but ...try for example, something like this:
>
> create or replace type myGroupType
> as object
> ( seq number,
> order_id varchar2(10),
> order_dt date,
> agent_id varchar2(10),
> percent number,
> group_seq number
> );
> /
>
> create or replace type myGroupTabType as table of myGroupType;
> /
>
> create or replace package mypkg
> as
> function sel return myGroupTabType PIPELINED;
> end;
> /
>
>
> create or replace package body mypkg
> as
> function sel return myGroupTabType
> PIPELINED
> is
> v_c number := 0;
> v_g pls_integer := 0;
> v_row myGroupTabType := myGroupTabType();
> v_old_id txn.order_id%TYPE;
> v_old_dt txn.order_dt%TYPE;
> v_old_ag txn.agent_id%TYPE;
> begin
> for x in (select * from txn order by seq)
> loop
> v_c := v_c + x.percent;
> if (v_c > 1 or x.order_id != v_old_id or x.order_dt !=
v_old_dt or x.agent_id != v_old_ag) then
> if (x.order_id != v_old_id) then
> v_g := 0;
> end if;
> for i in 1 .. v_row.count
> loop
> v_row(i).group_seq := null;
> pipe row (v_row(i));
> end loop;
> v_row := myGroupTabType();
> v_row.extend;
> v_row(v_row.count) := myGroupType(x.seq, x.order_id,
x.order_dt, x.agent_id, x.percent, null);
> v_c := x.percent;
> v_old_id := x.order_id;
> v_old_dt := x.order_dt;
> v_old_ag := x.agent_id;
> elsif (v_c = 1) then
> v_row.extend;
> v_g := v_g + 1;
> v_row(v_row.count) := myGroupType(x.seq, x.order_id,
x.order_dt, x.agent_id, x.percent, v_g);
> for i in 1 .. v_row.count
> loop
> v_row(i).group_seq := v_g;
> pipe row (v_row(i));
> end loop;
> v_row := myGroupTabType();
> v_c := 0;
> else
> v_row.extend;
> v_row(v_row.count) := myGroupType(x.seq, x.order_id,
x.order_dt, x.agent_id, x.percent, null);
> v_old_id := x.order_id;
> v_old_dt := x.order_dt;
> v_old_ag := x.agent_id;
> end if;
> end loop;
> for i in 1 .. v_row.count
> loop
> v_row(i).group_seq := null;
> pipe row (v_row(i));
> end loop;
>
> return;
> end;
> end;
> /
>
> select * from table (mypkg.sel());
>
>
> Anurag
Received on Tue Dec 28 2004 - 10:23:02 CST
