bin fitting question ( a little different than examples on the web)
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Aug 2013 11:47:32 -0400
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
