Re: Is a cursor the only way?

You can use the CASE statement if you create it as a VARCHAR2 string and use native dynamic SQL.

But I'd use a cursor loop just to make debugging and commenting easier.

Daniel Morgan

Jon Waterhouse wrote:

> I have a file of people which probably contains many people twice. I am
> trying to create a file containing likely matches In this match file I want
> to indicate which items actually match. My primary linking field is date of
> birth, and then I want to indicate whether there is also a match on SIN
> number, first name and/or last name.
>
> In MS SQL server I would have set this up as a CASE statement to create this
> variable. In Oracle the CASE statement is not supported in PL/SQL and DECODE
> will not work on constructed logical values (e.g. c1.SIN = c2.sin). I
> currently have this set up using a cursor and a FUNCTION (below) to return
> the value for the column, but it seems more procedural than should be
> necessary and I suspect it is not that efficient, either. Is there a way
> that I can set this up without a cursor in Oracle (using 8.1.6).
>
> Thanks,
> Jon
>
> FUNCTION set_matchtype(sin1 IN number ,sin2 IN number,fname1 IN nchar,fname2
> IN nchar,lname11 IN nchar ,lname12 IN nchar,lname21 IN nchar,lname22 IN
> nchar) RETURN number IS
> BEGIN
> if (sin1 = sin2 and sin1 is not NULL and sin1 <> 0) then
> RETURN 2;
> ELSE
> if (lname11 is not null and (lname11 = lname21 or lname11 =lname22)) or
> (lname12 is not null and (lname12 = lname21 or lname11 =lname22))
> then
> if (fname1 is not null and fname1 = fname2) then
> RETURN 6; -- first names an last names match
> else
> RETURN 5; -- just last names
> END IF;
> ELSE
> if fname1 is not null and fname1 = fname2 then
> RETURN 4; -- just first names
> ELSE
> RETURN 1; -- just dob
> END IF;
> END IF;
> END IF;
> END;
>
> PROCEDURE makematch IS
> CURSOR mc IS SELECT c1.id as id1,c2.id as id2,c1.sin as sin1,c2.sin as
> sin2,c1.fname as fname1,c2.fname as fname2,c1.lname1 as lname11,c1.lname2 as
> lname12,
> c2.lname1 as lname21,c2.lname2 as lname22,c1.role as role1,c2.role
> as role2 from children c1, children c2
> where c1.dob = c2.dob and c1.id < c2.id;
> matchval number(1);
> BEGIN
> FOR mc_rec in mc LOOP
> /*This should update each match record */
> matchval :=
> set_matchtype(mc_rec.sin1,mc_rec.sin2,mc_rec.fname1,mc_rec.fname2,mc_rec.lna
> me11,
> mc_rec.lname12,mc_rec.lname21,mc_rec.lname22);
> insert into matches (id1,id2,role1,role2,matchtype)
> VALUES(mc_rec.id1,mc_rec.id2,mc_rec.role1,mc_rec.role2,matchval);
> END LOOP;
> COMMIT;
> END;
Received on Mon Mar 04 2002 - 12:26:43 CST