Is a cursor the only way?

From: Jon Waterhouse <jonwaterhouse_at_mail.gov.nf.ca>
Date: Mon, 4 Mar 2002 13:18:36 -0330
Message-ID: <3c83a4f0.0@209.128.1.3>

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 - 10:48:36 CST