>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