Simple loop question

[acorbally]

I always struggle with looping to see when a value changes. Does anyone have a good sample I can copy for future reference?

I want to loop through a set of records that are ordered by let's say automobile make. The records are ordered and I want to count how many chevys vs how many fords, etc. I seem to strugle with how to loop and determine when the records change from chevy to ford. Keeping in mind I may want to perform calculations at the time in which I have counted all of the chevys. Make sense?

 

[Stella740pl]

Wouldn't it be easier to use SELECT-SQL for this particular task?

SELECT auto_mk, COUNT(*) ;
FROM myTbl ;
GROUP BY 1 ORDER BY 1 ;
INTO CURSOR all_cnts

Unless I misunderstood your requirement "to perform calculations at the time in which I have counted all of the chevys", or you want to do something not really suitable for SQL inside the loop, you will have all your counts ready at the same time.

 

[acorbally]

Sure it would but I commonly have to loop through records and do validation. My exact scenario is this:

Data example:

MODE START_DATE STOP_DATE
aaf 01/01/2004 / /
aaf 01/01/2003 12/31/2003
aaf 01/01/2001 12/31/2002
aar 01/01/2004 / /
aar 01/01/2003 12/31/2003
aar 01/01/2001 12/31/2002
wcv 01/01/2004 / /
wcv 01/01/2003 12/31/2003
wcv 01/01/2001 / /

I need to loop through these records and for the mode aaf determine the open ended dates. So for each record I want to evaluate something. There is more but that is a general idea. So what I want a sample of is looping through starting with aaf and going through to the end of the record set. When aaf changes to aar determine count, evaluate other items, etc. Do you have a simple loop to do that?

 

[Stella740pl]

You will need to write nested loops, e.g. inside SCAN...ENDSCAN use DO WHILE mode=somevalue...ENDDO, while keeping counters for whatever you need to accumulate. Don't have time to write samples now, but will look for one in my programs, or someone else might help.

 

[Stella740pl]

Here is an old piece of code, somewhat simplified, with both DO WHILEs, not SCAN, which goes through ordered records with the same index key and enumerates them:

SELECT tmp_block
GO TOP
DO WHILE !EOF("tmp_block")
	theKey=(((group*10000+unitnum)*10000000+run)*10+dr)*10^9
	new_ord=1
	REPLACE order WITH new_ord
	EndOfBlock=0 
	p_end=block_e
	SKIP
	DO WHILE EndOfblock=0
		IF (((group*10000+unitnum)*10000000+run)*10+dr)*10^9=theKey ;
						AND p_end=block_s
			new_ord=new_ord+1
			REPLACE order WITH new_ord
			p_end=block_e
			SKIP
		ELSE
			EndOfBlock=1
			EXIT
		ENDIF	
	ENDDO
ENDDO

 

[TheRambler]

Another way to do something similar to Stella740pl's first post:

old_mode   = 'ZZZ'  && dummy value
mode_count = 0
SCAN
   IF mode <> old_mode
      ? old_mode, mode_count
      old_mode   = mode
      mode_count = 0
   ENDIF
   mode_count = mode_count + 1
ENDSCAN
? old_mode, mode_count