Re: Cursors and stored procedures. Plz Help

sujit wrote:

> 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.

Any chance you could post the full error message text from SHOW ERR to help out those who might wish to help you out?

While you are at it ... the Oracle version would be a useful bit of information too.

Daniel Morgan Received on Wed May 15 2002 - 17:11:02 CDT