Dennis,
I think what you want is a PROCEDURE, not a FUNCTION. Here is an
example:
CREATE OR REPLACE PROCEDURE CreateFlagSequence IS
stored_ind_id number(8);
ind_first number(1);
cursor c1 is
select individual_id, activity_date , lead_grade
from tschierling.IND_ACTS
-- you don't want GROUP BY because you are not using aggregate
functions (SUM, AVG, COUNT)
-- GROUP BY individual_id, activity_date , lead_grade
ORDER BY individual_id, activity_date desc, lead_grade;
BEGIN
- 1. create new table, which we will perform UPDATE on.
- note: this is only table definition. Table will be empty to start
off.
- note 2: I think for PL/SQL DDL (CREATE or ALTER TABLE) there is
special syntax. I forgot it.
CREATE TABLE TSHCIERLING.IND_ACTS_FLAGGED
(individual_id VARCHAR(10) NOT NULL,
activity_date desc DATE,
lead_grade CHAR(1),
ind_first CHAR(1)
);
- 2. start the logic section - open cursor on IND_ACTS, loop through
cursor and insert
- into our new table.
stored_ind_id := 0;
FOR IND_ACTS in c1 LOOP
- initialize indicator flag
ind_first := 0;
- check if ID is first in sequence.
IF stored_ind_id <> IND_ACTS.individual_id THEN
ind_first := 1;
END IF;
- insert record from cursor into new table, including the indicator
flag
INSERT INTO TSHCIERLING.IND_ACTS_FLAGGED VALUES(
IND_ACTS.individual_id,
IND_ACTS.activity_date,
IND_ACTS.lead_grade,
ind_first);
stored_ind_id := IND_ACTS.individual_id;
END LOOP;
END;
dennis.pong_at_gmail.com wrote:
> Hi,
>
> I'm trying to flag the first record within each group of individual_id
>
> For example,
> INDIVIDUAL_ID FIRST_FLAG
> 1447620 7/30/2005 C 1
> 1447620 7/21/2005 C 0
> 1447620 7/15/2005 D 0
> 1447620 6/30/2005 D 0
> 1840354 12/1/2006 Z 1
> 1840354 11/28/2006 D 0
> 1840354 11/28/2006 D 0
> 1840354 11/28/2006 D 0
>
> HERE IS MY FUNCTION
>
> CREATE OR REPLACE Function First
> ( individual_id IN number )
> RETURN number
>
> IS
> stored_ind_id number(8);
> ind_first number(1);
>
>
> cursor c1 is
> select individual_id, activity_date , lead_grade
> from IND_ACTS
> GROUP BY individual_id, activity_date , lead_grade
> ORDER BY individual_id, activity_date desc, lead_grade;
>
>
> BEGIN
>
> stored_ind_id := 0;
> ind_first := 0;
>
>
> FOR IND_ACTS in c1
> LOOP
> If stored_ind_id <> individual_id then
> stored_ind_id := individual_id;
> ind_first := 1;
>
> END IF;
>
>
> END LOOP;
>
> RETURN ind_first;
>
> END;
>
>
> Don't know if someone could point out the possible flaws of this
> function that it doesn't work as expected.
>
> Thanks,
Received on Wed Dec 06 2006 - 17:16:59 CST
