Cursors and stored procedures. Plz Help

From: sujit <sujitk1_at_hotmail.com>
Date: 15 May 2002 14:54:18 -0700
Message-ID: <6f11e430.0205151354.4e0b0d38@posting.google.com>

Hi There,
The table cust_master is as follows:

CID   FNAME    LNAME     A1    A2      CTY      GENDER    
U371  NANCY    ALTAM     14  CHESTERF ROCKVILLE     UNKNOWN
UZ50  JUANI    CALDWELL     19  24TH AVE TEMPLEHILL  f    
UZ58  NIQUE    JACKSON     02  ST #31   LAUREL      f    
UZ50  JUANI    CALDWELL     19  24TH AVE TEMPLE HILL unknown
U373  NANCY    ALTAM     14  CHESTERF ROCKVILLE      f

The ouput required is as follows:
CID FNAME LNAME  A1  A2      CTY      GENDER  mkey

U371 NANCY ALTAM 14 CHESTERF ROCKVILLE f naltam14chesroc UZ50 JUANI CALDWELL19 24THAVE TEMPLEHILL f jcald1924th

The task is as follows:

a)The duplicate records in the table should be identified.
b)then a match key based on fname,lname,a1,a2,cty should be formed.
c)the duplicate records should be grouped based on the match key and

the following criteria should be applied to the records and then inserted into a new table.
If (the total number of duplicated records – number of records where gender = ‘UNKNOWN’) > 1 Then find the record with the maximum cust_program.enroll_date and use that value
Else If (the total number of duplicated records – number of records where gender = ‘UNKNOWN’) = 1

Then set gender = value that is not ‘UKNOWN’ Else

Set Gender = ‘UNKNOWN’ value

I wanted to solve this using a stored procedure which is as follows but am facing compiling errors .Can some one plz help me asap.Your help would be greatly appreciated.

CREATE OR REPLACE
  Procedure cust_master6 is
  cursor cu_tabrec2 is
   SELECT DISTINCT A.CUST_ID,A.LAST_NAME,A.FIRST_NAME,A.CITY,A.ZIP_CODE,A.ADD1,A.STATE,A.GENDER,A.RACE,A.AGE,    A.last_name||substr(A.first_name,1,1)||A.city||A.zip_code||substr(A.add1,1,4) MATCH_KEY
   FROM TEST100 A,TEST100 B where (A.STATE=B.STATE) AND
(A.ADD1=B.ADD1)AND (A.ZIP_CODE=B.ZIP_CODE)
   AND (A.CITY=B.CITY) AND (A.FIRST_NAME=B.FIRST_NAME)AND
(A.LAST_NAME=B.LAST_NAME) and A.CUST_ID<>B.CUST_ID

ORDER BY A.LAST_NAME, A.FIRST_NAME, A.CUST_ID, A.CITY, A.STATE; begin

     for tabrec in cu_tabrec2 loop
     INSERT INTO CUSTOMER_MASTER_TEST VALUES (tabrec.first_name);

END LOOP;
END; Plz help. Received on Wed May 15 2002 - 16:54:18 CDT