Re: What went wrong with this Function First?

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 - 18:53:48 CST