How do you skip (ignore) the first element in a SCAN? 14

[Manuch]

Can I start the SCAN from a specific index instead of always from the TOP?
By design the SCAN command always starts from the first element of the cursor, so this doesn't work

SELECT _cursor
GO 2
SCAN
   ? _cursor.id
ENDSCAN

Everytime I needed to skip an element I always had to resort to keeping a counter or something ugly:

LOCAL counter
counter = 0
SELECT _cursor
SCAN
   counter = counter + 1
   If counter == 1 Then
	SKIP
   EndIf
   ? _cursor.id
ENDSCAN

Is there a proper way in VFP to specify where SCAN should start?
Or is there a way to make SCAN set the current index he is looping to a variable (and not having to keep a counter myself)?


Thank you,
Emanuele.

 

[ManniB]

I think the purpose of SCAN is not having to take care of things like SKIP, GO TOP and reselecting the workarea. If you need to skip rows in the first place it might be a bit more convenient to use a DO WHILE...ENDDO instead of SCAN with a counter.

SELECT _cursor
Go 2
DO WHILE NOT EOF()
* some code here
SELECT _cursor
SKIP
ENDDO

Regards,
Manni

 

[Chris Miller]

You can position where you want and use SCAN REST.

It helps to know all the scopes. There's FOR RECNO()>1, too, OR WHILE and others.

Chriss

 

[Mike Lewis]

Emanuele,

As you have seen, a SCAN (by itself) always starts at the top of the table. However, there are plenty of other solutions:

SCAN FOR RECNO() >= 2

or

SCAN
  IF RECNO() = 1
    * ignore first record
    LOOP
  ENDIF

...

ENDSCAN

or

GO 2
SCAN REST

and probably some others.

But all of these assume that the table is not indexed, and that the first record is not deleleted or out-filtered.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Manuch]

Thank you very much, excellent answers, I didn't know about SCAN REST, also never came in my mind to simply use RECNO().

 

[Steve Meyerson]

But all of these assume that the table is not indexed...

Or - If indexed, you could
GO TOP
SKIP
SCAN REST

Steve

 

[Gerrit Broekhuis]

Emanuele,

How about using a cursor for this (select yyy into cursor xxx readwrite).
Create and set the index.
Delete the first record.
Do your SCAN…ENDSCAN.

Regards, Gerrit

 

[Mike Lewis]

How about using a cursor for this

That might be worth considering. But it wouldn't be so easy if he needed to update the original table within the SCAN loop.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ManniB]

Great to know about SCAN REST and that it will always work (fast).

Then

SELECT _cursor
LOCATE && faster than Go top
SKIP
SCAN REST
? _cursor.id
ENDSCAN

would be the/fastest way to skip the first record I guess...

 

[Mike Lewis]

A few other points about SCAN loops that might be of interest (not necessarily relevant to the original question):

1. The ENDSCAN always re-selects the original work area. In other words, if you change the work area inside the loop, it is not necessary to explicitly change it back again (although it does no harm if you do).

2. SCAN without any scope, FOR or WHILE clause always processes the entire table. So there is no need to GO TOP before starting the scan.

3. According to HackFox, if you are using a FOR clause, you'll get better performance if you do SET ORDER TO 0 before the start of the scan.

4. If there is an index in force, and if you change the value of an index field inside the scan, the changed record will move to its new position in the index, and this might upset the order in which the records are processed. Best to avoid doing that if possible.

5. Before SCAN / ENDSCAN was invented, we used DO WHILE NOT EOF() / SKIP / ENDDO. You can still do that (of course) but in most cases SCAN / ENDSCAN will be faster.

I hope the above will be useful.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

ManiB,

SELECT _cursor
LOCATE && faster than Go top
SKIP
SCAN REST
? _cursor.id
ENDSCAN

That is correct, and will skip the first record with whatever index sort order is set for ordering of the data. LOCATE also will take that ordering into account. And both LOCATE and SKIP will also take into account any FILTER set. In such cases with index and filter, as Mike Lewis already said the FOR scope will have disadvantages, And Mike Yearwood also is correct that FOR RECNO()>1 would only be optimized when an index on RECNO() exists, which you'd usually not have. The FOR RECNO()>1 I gave was mainly pointing out that SCAN supports more scopes then the full scope or SCAN REST, there is FOR, NEXT and WHILE, so you can also only SCAN the Top 10 records with LOCATE an SCAN NEXT 10.

In very short, once more: Pay attention to record scopes existing, besides FOR and ALL, there's NEXT and WHILE and people don't know about these options very often. So you have very many options to control which records are processed with these scope clauses instead of needing IF statements within the loop body and then LOOP to skip a go back to SCAN instead of continuing in the loop body code. But that in itself is also worth knowing, just like you can EXIT from a scan loop like you can from a FOR or DO WHILE loop, too.

All this is just basic knowledge about record scopes (not variable life scopes) and loops in general and you usually learn this as one family of knowledge that belongs to be known together.
One further thing that belongs to the topic of filtering is that you can also filter to records with the same index key using SET ORDER together with SET KEY. And that can be combined with SET FILTER or a FOR clause, too. It binds you to usage of the index that will also determine the order overall, but then SET KEY will usually be used for a single key, which makes sense to loop through all records with the same foreign key. It makes less sense with a primary key, as that's just one record then, which requires no loop.

And to finish this SET KEY ca be used with a RANGE option, too.

All in all, you have so many options, that you will only need IF logic in the loop body for rare complicated cases. You can combine SCAN with its record scope clauses with a SET ORDER, SET FILTER and SET KEY [RANGE]. And then you can also define Relations from one workarea to others and have SET SKIP, which means you can loop through a set of workareas with related data even without nesting SCAN loops.

All this together and only ALL this together enfolds how xBase can do things for which you would otherwise need SQL. And still SQL can be better in other cases. Usually when you mainly just want the resultset and not process the records that belong to the result set within the loop body. Well, and that processing within the loop also has to be done with caution, as Mike Lewis already pointed out, since manipulating data while you scan through it with scopes and order and filters, can change your current position within the set of records you loop through.

I remember people saying they still prefer the old DO WHILE !EOF() with SKIP because that gives them more or full control, and you can then conditionally not SKIP or SKIP 2 or whatever. Well, you are still allowed to SKIP in a SCAN loop, too, you just need to keep in mind a SKIP 1 is automatic from the ENDSCAN, so to Skip 1 row from processing you SKIP 1 and not SKIP 2. But it also looks more normal to me to SKIP 1 record, for example. So all inn all, that should tell you why everyone should be a big fan of the SCAN loop over the DO WHILE loop. You can simply express your plan of scanning with all the scope clauses that would otherwise need an IF, or even several IFs and conditional SKIPs and what not, that's you can all very precisely an elegantly specify in the record scopes clauses of a SCAN..ENDSCAN loop.

Chriss

 

[ManniB]

Mike,

thank you for your summary about the SCAN...ENDSCAN command, I wasn't aware of point 3 and 4. That changing the value of an index field in a scan loop can change the record position is certainly somehting to look out for and avoid!

Chris,

thanks for your insights regarding SCAN loops and the various Scopes that can be used. I often use SET ORDER and then SET KEY, because unlike SET FILTER, SET KEY will be fast and I noticed if you have a grid with table as the datasource (instead of a cursor) then it won't hurt the behavior of the vertical scrollbas like SET FILTER does, provided you take care of a few other things like not having deleted records inside the key while having SET DELETED ON. However, I had the problem that, despite using SET KEY the grid's vertical scrollbars no longer worked well if the key refers to several fields of the table instead of just one. But that's completely off topic.

It is very interesting becoming more apparant of all the capabilities of xBASE. SET RELATION and SET SKIP is somehting I have never used so far, if you avoid nested SCANS that would provide another great opportunity to speed things up.

I will pay attention to using scopes with SCAN loops in the future and check whether it allows to save one or the other IF clause...

Thanks,
Manni

 

[ManniB]

Thank you, Mike!

 

[tarun2790]

Thanks, MIke. Your solution works for me.

MSBI Online Training

https://www.igmguru.com/data-science-bi/msbi-certification-training/