Re: What went wrong with this Function First?

From: <dennis.pong_at_gmail.com>
Date: 6 Dec 2006 17:00:52 -0800
Message-ID: <1165453252.713717.16650@j44g2000cwa.googlegroups.com>

I really need to output a value based on whether it's a first record by its by-variable. So that's why I need to do it with a function. Thanks anyways.

-DP

Charles Hooper wrote:
> 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,

>

> Have you considered doing the above using only SQL? It can be
> accomplished very easily.
> The set up:
> CREATE TABLE IND_ACTS(
> INDIVIDUAL_ID NUMBER(12),
> ACTIVITY_DATE DATE,
> LEAD_GRADE VARCHAR2(2));

>


> INSERT INTO IND_ACTS VALUES (1447620,'30-JUL-2005','C');

> INSERT INTO IND_ACTS VALUES (1447620,'21-JUL-2005','C');

> INSERT INTO IND_ACTS VALUES (1447620,'15-JUL-2005','D');

> INSERT INTO IND_ACTS VALUES (1447620,'30-JUN-2005','D');

> INSERT INTO IND_ACTS VALUES (1840354,'01-DEC-2006','Z');

> INSERT INTO IND_ACTS VALUES (1840354,'28-NOV-2006','D');

> INSERT INTO IND_ACTS VALUES (1840354,'28-NOV-2006','D');
> INSERT INTO IND_ACTS VALUES (1840354,'28-NOV-2006','D');

>

> COMMIT;

>

> SELECT
> INDIVIDUAL_ID,
> ACTIVITY_DATE,
> LEAD_GRADE
> FROM
> IND_ACTS;

>


> INDIVIDUAL_ID   ACTIVITY_DATE   LEAD_GRADE

>       1447620   30-JUL-2005 00:00:00   C

>       1447620   21-JUL-2005 00:00:00   C

>       1447620   15-JUL-2005 00:00:00   D

>       1447620   30-JUN-2005 00:00:00   D

>       1840354   01-DEC-2006 00:00:00   Z

>       1840354   28-NOV-2006 00:00:00   D

>       1840354   28-NOV-2006 00:00:00   D

>       1840354   28-NOV-2006 00:00:00   D

>

> With the data in the table, we first need to determine the order of the
> rows when separated by INDIVIDUAL_ID. This can be accomplished using
> the ROW_NUMBER analytic function. Note: I added an order by clause to
> the SQL statement just to show that the ROW_NUMBER analytic function is
> not affected by the ORDER BY at the end of the SQL statement.
> SELECT
> INDIVIDUAL_ID,
> ACTIVITY_DATE,
> LEAD_GRADE,
> ROW_NUMBER() OVER (PARTITION BY INDIVIDUAL_ID ORDER BY ACTIVITY_DATE
> DESC,LEAD_GRADE) ROW_IN_GRP
> FROM
> IND_ACTS
> ORDER BY
> INDIVIDUAL_ID,
> ACTIVITY_DATE;

>


> INDIVIDUAL_ID   ACTIVITY_DATE     LEAD_GRADE   ROW_IN_GRP

>       1447620    30-JUN-2005 00:00:00    D                   4

>       1447620    15-JUL-2005 00:00:00    D                   3

>       1447620    21-JUL-2005 00:00:00    C                   2

>       1447620    30-JUL-2005 00:00:00    C                   1

>       1840354    28-NOV-2006 00:00:00   D                  4

>       1840354    28-NOV-2006 00:00:00   D                  2

>       1840354    28-NOV-2006 00:00:00   D                  3

>       1840354    01-DEC-2006 00:00:00   Z                   1

>

> Looking at the above, you only want to return the 1s, and set all other
> numbers to 0 - this can be accomplished with a DECODE function:
> SELECT
> INDIVIDUAL_ID,
> ACTIVITY_DATE,
> LEAD_GRADE,
> ROW_NUMBER() OVER (PARTITION BY INDIVIDUAL_ID ORDER BY ACTIVITY_DATE
> DESC,LEAD_GRADE) ROW_IN_GRP,
> DECODE(ROW_NUMBER() OVER (PARTITION BY INDIVIDUAL_ID ORDER BY
> ACTIVITY_DATE DESC,LEAD_GRADE),1,1,0) IS_FIRST
> FROM
> IND_ACTS
> ORDER BY
> INDIVIDUAL_ID,
> ACTIVITY_DATE;

>


> INDIVIDUAL_ID   ACTIVITY_DATE     LEAD_GRADE   ROW_IN_GRP   IS_FIRST

>       1447620    30-JUN-2005 00:00:00    D                   4

>             0

>       1447620    15-JUL-2005 00:00:00    D                   3

>             0

>       1447620    21-JUL-2005 00:00:00    C                   2

>             0

>       1447620    30-JUL-2005 00:00:00    C                   1

>             1

>       1840354    28-NOV-2006 00:00:00   D                  4

>           0

>       1840354    28-NOV-2006 00:00:00   D                  2

>           0

>       1840354    28-NOV-2006 00:00:00   D                  3

>           0

>       1840354    01-DEC-2006 00:00:00   Z                   1

>            1

>

> For performance, use just SQL, if at all possible, rather than using a
> PL/SQL function or procedure.

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Wed Dec 06 2006 - 19:00:52 CST