Re: Creating sequence for group by in a query?

From: Andreas Sheriff <spamcontrol_at_iion.com>
Date: Thu, 29 Jun 2006 12:16:46 -0700
Message-ID: <NGVog.1188$RD.1172@fed1read08>

"Ron" <tachoknight_at_gmail.com> wrote in message news:1151604869.117913.254110_at_d56g2000cwd.googlegroups.com...
> Hi all-
>
> I am trying to insert into a table that takes two number fields. The
> first field I want to generate with a sequence, the second is an ID.
> The issue is that the base data has a date field. So I have a table
> like:
>
> ID ENTERED_DATE
> 32423 5/5/06
> 34644 5/5/06
> 11907 6/1/06
> 95996 6/16/06
> 94311 6/16/06
>
> So that the table I'm inserting looks like:
>
> GROUP ID
> 1 32423
> 1 32423
> 2 11907
> 3 95996
> 3 94311
>
> So the sequence is based on the group by of the entered_date field. Is
> this possible to do in a single sql statement?
>
> Thanks,
>
> Ron
>

... Just off the top of my head ...

Use a package to maintain state and to get the next value of the sequence if the date changed:

create sequence myseq ...;

create or replace package as
procedure init;
function getSeqNum(mydate basetable.entered_date%type) return newtable.id.%type;
end;
/
create or replace package body as
mydate basetable.entered_date%type := null; currseqnum number:= null;
procedure init as
begin

    mydate := null;
end;
function getSeqNum(currdate basetable.entered_date%type) return newtable.id.%type is
begin

    if mydate is null || mydate != currdate then

        /* Get the next sequence number */
        select myseq.nextval into currseqnum from dual;
        mydate := currdate;

    end if;
    return currseqnum;
end;
end;
/

Use the function getSeqNum() to see if the date has changed. If it has, then the function will return the next value of the sequence. I haven't actually tried this bit of code so it may need a little massaging.

Is this the behavior you were seeking?

Disclaimer: If there are any typos or syntactical inconsistencies, then I blame it on the lack of caffeine.

-- 
Andreas
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer


"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding if you don't eat your meat?!?!"
---

WARNING:
DO NOT REPLY TO THIS EMAIL
Reply to me only on this newsgroup 

Received on Thu Jun 29 2006 - 14:16:46 CDT