bin fitting question ( a little different than examples on the web)

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Thu, 29 Aug 2013 11:47:32 -0400
Message-ID: <CAE-dsO+93aV2__KGkdm9xLU24w2X00wNT1NhQEGL9RPiz+2k5w_at_mail.gmail.com>

11.2.0.3. I need to do some bin sorting. I saw Jonathan Lewis's page, the asktom discussion, and toms summary of it in Oracle Magazine. I need to do something a little different. I am trying to figure out how to do a certain piece of it.
My initial hope was that I could use row_number,dense, or dense_rank and a window clause, but when I did this, I got errors and googled it. Window Clauses are not support with these functions.

Is there a way to do this in the SQL Model clause? Or another way? If there is another way, can I call this from a model clause? I just started digging into the model clause. So I don't know it real well.

Basically I need to:

partition by a varchar field. (for test purposes the values are just A,B,C,D)
With in each partition, I need to count from 1 to 50. For each cycle, I need to increase another counter.

That probably doesn't make sense, so let me do it in pseudo-code This won't compile...
declare
 cursor mySQL is
   select field1 from my bigtable;
  cnt number :=0;
  groupcnt number := 1;
  lastField1 mybigTable.field1%type;
begin
for i in mySQL loop
if nvl(lastField1,0) = i.field1 then
  if cnt <= 50 then

     cnt := cnt+1;
  end if;
  else

     cnt := 1;
     groupcnt := group+1;

   end if;
 else

     cnt :=1
     groupcnt := 1;

 end if;
end loop;

There would be business logic in here. So basically the output would be

Field 1 CNT GROUPCNT

A             1          1
A ...        50          1    -- CNT would have 2-49 as well
A              1          2
B               1         1

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 29 2013 - 17:47:32 CEST