scan problem... 1

[Mandy_crw]

i have used SCAN command,, sometimes it scans every record that return .t., but most of the time, only the first record that matches criteria... please help...

SCAN FOR ALLTRIM(Q) = ALLTRIM(trans.idnum)

REPLACE trans.idnum WITH ALLTRIM(R)
CTR = CTR + 1

ENDSCAN

 

[Chris Miller]

What workarea do you scan?

If you scan trans, then most probably only one trans records idnum is Q.
If you scan any other workarea, the for condition doesn't relate to it, so you either scan all or no rows, because if the current record of trans matches with Q, that's true as long as you don't move in trans.

As you replace trans.idnum I assume you scan trans. By the way, then the REPLACE can also be Replace idnum With Alltrim(R)

Caution, since you alter trans, the scan could continue elsewhere, if trans is orderedby idnum, for example, and you change idnum to something that's higher than it was before, you skip rows. That's the crux of changing the data while you scan it. This can only work when trans isn't ordered. Then scanning is in record number order and changing anything in trans doesn't change the record number.

Chriss

 

[Chris Miller]

I would change this to SQL:

Update trans set idnum=ALLTRIM(R) Where ALLTRIM(Q)=ALLTRIM(idnum)
CTR=_TALLY

And you might want to use == instead of =. Think of an example like Q='ABCD', idnum has rows with 'ABC' and 'ABCD', then still both of these match Q:

? ALLTRIM('ABCD   ')=ALLTRIM('ABC    ')

At least in default mode of EXACT. Also EXACT doesn't change SQL behavior, that's goverened by the ANSI setting.

To make it easy, just expand all values to the length of the idnum field, then comparisons are exact matches anyway:

R = PADR(R,Len(trans.idnum,' ')
Q = PADR(Q,Len(trans.idnum,' ')
Update trans set idnum=R Where idnum=Q
CTR=_TALLY

Chriss

 

[Mandy_crw]

Hi Chriss.... I now understand the scan... I've used your suggested SQL and it worked!!! thanks for helping me always....

 

[Mike Lewis]

Mandy,

In addition to the advice that Chris has given you, be aware that there is a subtle problem with REPLACE that might be catching you out. (This has nothing to do with SCAN; it's something you might come across at any time.)

Consider this command:

REPLACE Trans.idnum WITH ALLTRIM(R)

If Trans is the selected work area at the point at which this command is executed, then it should work as expected. But if a different work area is selected, then the command might fail to do the update. That's because, by default, REPLACE is scoped to the current record in the current work area. If the table in that work area happens to be sitting on end of file, then there is no record for the command to operate on, and therefore it will do nothing.

For that reason, it is always advisable to explicitly select the correct work area before doing the REPLACE; or, better, using the IN clause:

REPLACE Trans.idnum WITH ALLTRIM(R) IN SomeWorkArea

None of this invalidates Chris's suggestion of using UPDATE instead of REPLACE. But it is definitely something you should keep in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rick C. Hodgin]

i have used SCAN command,, sometimes it scans every record that return .t., but most of the time, only the first record that matches criteria... please help...

SCAN FOR ALLTRIM(Q) = ALLTRIM(trans.idnum)
    REPLACE trans.idnum WITH ALLTRIM(R)
    CTR = CTR + 1
ENDSCAN

To do it with XBASE commands, try this:

USE (FULLPATH(DBF())) AGAIN ALIAS _update IN 0
SELECT _update
SCAN FOR ALLTRIM(Q) = ALLTRIM(idnum)
    REPLACE idnum WITH ALLTRIM(R)
    CTR = CTR + 1
ENDSCAN
USE IN _update

In that way, you will go sequentially without any indexes adjusting your position, but your original alias will still be there with all of its indexes in tact.

--
Rick C. Hodgin

 

[Chris Miller]

Rick,

don't forget SQL also always happens in a separate workarea, no matter if the name you address is already an alias of a currently used workarea or the sql engine finds it as dbf file or table name of the currently set dbc.
So even in case of an alias you select from, update or insert into DBF('alias'), not alias itself. So you don't get influenced by index, filter, position and you don't influence index order, filter and position. The effect on data can of course be in the alias workarea, as it is about dbf('alias'), no matter if that's a tmp cursor/view file or a real dbf file.

Sometimes it would be nice to let filter have an effect on SQL, but once you internelaze the very general truth SQL always works in separate workareas, you don't need to fear repositioning, getting positioned to EOF, having another order set, causing implicit tableupdate in row buffering mode because the record pointer moves or anything else you could fear if the SQL engine would use the alias itself.

It's not becoming that obvious because - as said - surely you see the effefct of sql inserts or updates in the alias. But whatever was done, was done on either the dbf (which in buffering mode can mean you don't see an update in the alias itself, but only as curval) or done on dbf('alias') which is actually the same as the alias, still a different workarea was used, so that alias - whatever it really is - is used again.

It's a very essential thing to keep in mind: SQL - own workareas. Any SQL. The special case also exists, if you query with sqlbuffering, the buffer of the specified alias is also taken into account. Still the actual SQL is done in a separate workarea and you don't have to fear any interference aside of the wanted interference (in case of inserts or updates, of course. In case of selects you get the selected data in a new workarea anyway, and don't effect the alias, but only read from it).

Chriss

 

[Rick C. Hodgin]

Can't remember if all SQL commmands need this to do exact matches or not:

lcSaveAnsi = SET("ANSI")
SET ANSI ON
UPDATE trans ;
    SET idnum = ALLTRIM(R) ;
    WHERE ALLTRIM(Q) == ALLTRIM(idnum)
SET ANSI &lcSaveAnsi

--
Rick C. Hodgin

 

[Mandy_crw]

Thanks for all your input Chris and Rick... unfortunately when I remove the order in the table, it causes "Field Phrase not found" error.... nonetheless the app is now working... thanks everyone for helping...

 

[Chris Miller]

You don't need to remove the order.

No idea what "phrase" causes that error, usually that refers to the situation of a control with a rowsourcetpye of fieldlist and the fieldlist, is, well, a fieldlist, and has no index tag names nor does it depend on any order.

But indeedyou don't need to unset the order, just do either SQL or scan another additional use of the same workarea. That's what's Ricks AGAIN is about. You don't scan the workarea trans, but first use it again in another workarea. This would also work with USE DBF('trans') IN 0 AGAIN ALIAS scantrans, then SELECT scantrans and then do the scan loop on scantrans unordered.

Chriss

 

[Mike Lewis]

Can't remember if all SQL commmands need this to do exact matches or not

As you know, Rick, xBase commands are sensitive to SET EXACT. But SQL commands are sensitive to SET ANSI. This works in a similar way to SET EXACT, but with some subtle differences.

unfortunately when I remove the order in the table, it causes "Field Phrase not found" error

Mandy, are you sure that error message is related to this particular problem? The only times I have seen that error message is when the RowSource in a list box or combo box is in the wrong format. If you are working with a list box or combo box during this process, that's where you should be looking for the cause of the error.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mandy_crw]

Hi Mike... I really dont know... it appears everytime i remove the order... thanks...

 

[Chris Miller]

What do you do to remove the order? Do you use the table again without order clause?

Then that's causing the error. You can just SET ORDER TO 0 or SET ORDER TO. That's without anything after TO. Of course trans, has to be selected for this.

But the UPDATE-SQL is fine.

Chriss

 

[Mandy_crw]

Oh I see Chris...I'll do that... thanks again Chris...