Currently I have it as a single loop statement... unfortunately it is taking 2 hours on a 800Mhz system to run. I ran the SQL's and it took about 6 minutes.


>>I think I am going braindead trying to figure this out.
>>
>>I have to compare 47,000 records (I'll call BM) to 8,500 records (I'll call SD) to find all the possible matches between them. There maybe:
>>a) no match in SD for each record in BM
>>b) maybe a single match in SD for each record in BM
>>c) maybe multiple matches in SD for each record in BM
>>
>>The end result is I need a table that contains each record in BM listing all the potential matches to SD (or no match all).
>>
>>I have 3 SQL statements that do the match and work independantly. I have tested each and they do as required. They are:
>>
>>--------------------------------------
>>** read EXACT matches
>>select * from bmdata left join sddmdata ;
>> on left(&bmzip,5)=left(sddmdata.sdzip,5) ;
>> where !empty(trim(sddmdata.sdaddr)) and ;
>> upper(trim(&bmlname))=upper(trim(sddmdata.sdlname)) and ;
>> upper(trim(&bmaddr))=upper(trim(sddmdata.addr)) into cursExact
>>
>>** read PROBABLE matches
>>select * from bmdata left join sddmdata ;
>> on left(&bmzip,5)=left(sddmdata.sdzip,5) ;
>> where !empty(trim(sddmdata.sdaddr)) and ;
>> upper(trim(&bmaddr))=upper(trim(sddmdata.addr)) into cursProb
>>
>>** read POSSIBLE matches
>>select * from bmdata left join sddmdata ;
>> on left(&bmzip,5)=left(sddmdata.sdzip,5) ;
>> where !empty(trim(sddmdata.sdaddr)) and ;
>> val(trim(&bmaddr))=val(trim(sddmdata.addr)) into cursPoss
>>-----------------------------------------------------
>>
>>I believe my next step (or modifications to the above) is to combine these into a single cursor. The key here is hierachy. Keep in mind, I have stored the RECNO for each BM record into the cursors:
>>
>>- if a BM record exists in cursExact then keep that record and ignore any matches in cursProb & cursPoss for that BM record number
>>- if a BM record exists in cursProb and not in cursExact, keep that record and ignore any matches in cursPoss for that BM record number
>>- if a BM record exists in cursPoss and not in cursProb or cursExact, keep that record for that BM record number
>>
>>
>>
>>When I have the ending cursor, I need to then finally compare the cursor with the original BM table:
>>
>>- if the BM record (by recno()) has no match in the end cursor, then keep the BM record
>>- if the BM record (by recno()) has at least one match in the end cursor, delete the orginal record in BM and add ALL the matching records from the end cursor.
>>
>>
>>No matter what, the ending table (BM) will have at least the original records with no matches, or most likely, a list of all the non-matches and matches. Those records will then be used in a grid grouping on the original BM number.
>>
>>
>>
>>
>>I hope this all makes sense - probably why it is driving me nuts!!! HELP!!!
>
>Peter,
>Have you ever thought of nonSQL processing. It might be easier and faster.
>
>

lnAddr=fsize(bmaddr,'BMData')
>lnSDLName=fsize(bmlname,'BMData')
>use SDData in 0 again alias ExMatch
>index on left(sdzip,5)+padr(addr,lnAddr)+padr(sdlname,lnSDLName) tag ExMatch
>use SDData in 0 again alias ProbMatch
>* index
>use SDData in 0 again alias PossMatch
>* index
>select BMData
>set relation to ExactExpr into ExMatch, ;
>ProbExpr into ProbMatch, ;
>PossExpr into PossMatch
>
>scan
> do case
>   case !eof('ExMatch') && Exactmatch found
>*
>   case !eof('ProbMatch') && Probable match found
>*
>   case !eof('PossMatch') && Possible match found
>*
>   Otherwise && No match
>endscan

Cetin