Re: SQL question...

From: Troy Tinnes <q10641_at_cig.nml.mot.com>
Date: Mon, 24 Jan 2000 08:55:23 +0900
Message-ID: <388B94EB.CB5C6EF5@cig.nml.mot.com>

PL/SQL will make this easy for you.

Step 1: Make a cursor that gets all of the TableA records which have matching locations in TableB.

CURSOR location_match IS

     SELECT firstname, streetname, streetno, houseno, city
     FROM    TableA
     WHERE  EXISTS
        (SELECT NULL
         FROM    TableB
         WHERE  TableA.streetname = TableB.streetname
         AND       TableA.streetno = TableB.streetno
         AND       TableA.houseno = TableB.houseno
         AND       TableA.city= TableB.city);

Step 2: Make a cursor to retrieve the TableB records ...

CURSOR B_Records IS

   SELECT firstname
   FROM TableB
   WHERE streetname = name

    AND      streetno     = no
    AND      houseno    = h_no
    AND      city           = c;

Step 3: loop through records using the following routine:

OPEN location_match;
LOOP

      FETCH location_match INTO TableA_name, name, no, h_no, c;
      EXIT WHEN location_match%NOTFOUND;
      OPEN B_records;
      LOOP
           FETCH B_Records INTO TableB_name;
           EXIT WHEN B_Records%NOTFOUND;
           x := 1;
           LOOP
                EXIT WHEN x > LENGTH(TableB_name);
                IF x = 1 THEN
                    compare_names :=
'?'||substr(TableB_name,2,LENGTH(TableB_name)-1);
               ELSIF x = LENGTH(TableB_name) THEN
                    compare_names :=
substr(TableB_name,1,LENGTH(TableB_name)-1)||'?';
               ELSE
                    compare_names :=
substr(TableB_name,1,x-1)||'?'||substr(TableB_name,x-1,x+1);
               END IF;
               IF TableA_name LIKE compare_names THEN
                   TableB_name := TableA_name;
                   <do update ...>
                   x = LENGTH(TableB_name)+1;
               ENDIF;
          END LOOP;
     END LOOP;
     CLOSE B_records;

END LOOP;
CLOSE Location_match;

PS. The IF statements within the internal LOOP (ie. IF x=1 ... IF x=LENGTH(_) are a pretty common routines when doing a char by char comparison and replacement. I always use this when removing (what I call) 'ghost' characters from data imported from another application that may have added some 'extra' ASCII anywhere from 1-20 into the data fields.

• Troy

Sayoni wrote:

> Hi guys,
> I was wondering if anyone can help me write this query..I am a newbie and
> don't really know the intricacies yet...
>
> I have two tables....
> TableA (id int, firstname,streetno, streetname,houseno,city)
> TableB (id int, firstname,streetno, streetname,houseno,city)
>
> TableA has all "correct" data and TableB may have some spelling mistakes in
> the firstname. eg. it may have 'Sudan' instead of 'Susan' . Now what I would
> like to do is, look through TableB and try to match the firstname with the
> firstname of TableA where everything else matches except the firstname.
>
> So basically
> TableA.streetname = TableB.streetname and
> TableA.streetno = TableB.streetno and
> TableA.houseno = TableB.houseno and
> TableA.city= TableB.city and
> substr(TableA.firstname,1,1) = substr(TableB.firstname,1,1)
>
> ..then go about replacing one letter at a time and match it with the rest.
> If it matches then basically I have found a match...
> eg...
> Find the length of the firstname and loop through the firstname length no of
> times.
>
> In the first round I will replace 'S?dan' and match it with 'S?san'
> it does not match so I loop through again and say 'Su?an' and match it with
> 'Su?an'
> it matches so i exit the loop and consider it matched.
>
> does it all make sense? thanks a lot in advance.
>
> Sayoni Mookerjee
> Snr. Analyst Programmer
> MOS Data, North Sydney
> (Work) : +61 (02) 9510 1370
> (Mobile) :+61 (041) 322 1277
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sun Jan 23 2000 - 17:55:23 CST