Instead, If I try this code it seems to work some faster

Update New_bills Set Exchange=(Select Exchange From Master Where Master.Accountno=New_bills.filename) and after getting the name of exchange in My New_bills I will Update Newpath.

But the problem using the above command is that I get NULL Values in the field exchange for the filename with are not accountno in Master.dbf, Instead of NULL Values I need 'MISSING EXCHANGE'.

I.e for the values for which there is match I need the proper exchange code and for which there is no match I need 'Missing exchange'

The SQL code which you provided earlier perhaps dont take E into consideration. E=ALLTRIM(Exchange) in my origininal code.

Kinldy Guide

Harsh






>>I am using the followin Scan Endscan loop to update the Target Path in a cursor.
>>But since my records are very huge, the processing is taking long-long times for updating.
>>
>>Can anybody give me equivalant SQL that can work faster.
>>
>>Here is my code
>>
>>

>>*This Program Will Update New_bill.dbf with New Target Path
>>SELECT New_bills
>>LOCATE
>>SCAN WHILE .NOT. EOF()
>>F=Filename
>>SELECT Master
>>LOCATE FOR accountno=F
>>IF FOUND()=.T.
>>E=ALLTRIM(Exchange)
>>ME=1 && Denotes that the exchgange is Not missing
>>ELSE
>>E="Missing Exchange"
>>ME=0 && Denotes that the exchange is Missing
>>ENDIF
>>
>>SELECT New_bills
>>P='&tDirectory'+'&pBillmonth'+'-'+ALLTRIM(STR(pbillyear))+'\'+E+'\'+STR(filename)+'.ps'
>>Replace Newpath WITH P
>>Replace Missexg WITH ME
>>Disp_percent=Alltrim(Str(Recno()/RecordsForSelectA*100))
>>Wait Wind "Step-3 [Updating Target Path Information..."+Disp_percent+"%"+" Completed]"  Nowait
>>ENDSCAN
>>RETURN
>>

>>
>
>Even without switching to SQL, there's a lot you can do to clean this code up. In terms of speed, the biggest issue is to get rid of the macros in the calculation of P. You _never_ need macros inside quote marks. That line can be:
>
>P=tDirectory+pBillmonth+'-'+ALLTRIM(STR(pbillyear))+'\'+E+'\'+STR(filename)+'.ps'
>
>You can also drop the LOCATE and the "WHILE NOT EOF()" in the SCAN line. SCAN scans the whole table by default, so no GO TOP or equivalent needed and no test for EOF needed.
>
>You an also change:
>
>IF FOUND()=.T.
>
>to:
>
>IF FOUND()
>
>though I can't imagine that affects speed.
>
>As for doing this with SQL, I think what you're doing is:
>
>

>cPath = ADDBS(tDirectory+pBillmonth+'-'+ALLTRIM(STR(pbillyear)))
>
>* Handle the ones that match first.
>UPDATE NewBills ;
>  SET MissExg = 1, ;
>          NewPath = FORCEPATH(FORCEEXT(FileName, "PS"), m.cPath) ;
>  FROM Master ;
>  WHERE NewBills.FileName = Master.AccountNo
>
>* Handle the ones with no match.
>UPDATE NewBills ;
>  SET MissExg = 0, ;
>          NewPath = "Missing Exchange";
>  WHERE FileName NOT IN (SELECT AccountNo FROM Master)
>

>
>If this isn't exactly it, it should send you in the right direction.
>
>Tamar