Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What went wrong with this Function First?

Re: What went wrong with this Function First?

From: KentL <kentlewan_at_yahoo.com>
Date: 6 Dec 2006 15:16:59 -0800
Message-ID: <1165447019.045076.304920@16g2000cwy.googlegroups.com>


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

CREATE TABLE TSHCIERLING.IND_ACTS_FLAGGED

	(individual_id VARCHAR(10) NOT NULL,
	 activity_date desc DATE,
	 lead_grade CHAR(1),
	 ind_first CHAR(1)
	 );

stored_ind_id := 0;

FOR IND_ACTS in c1 LOOP

  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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US