Simulating Scan Endscan with SQL 1

[white605]

Is there a way to write a query that will eliminate the scan-endscan in the following code? The following code works but with large table it chews for a while.

*original table -
SET SAFETY off
CLOSE all
Create Table inter1 (slgno C(5), lots c(4))
Insert Into inter1 (slgno,lots) Values ('8001a',"")
Insert Into inter1 (slgno,lots) Values ('9001a',"")
Insert Into inter1 (slgno,lots) Values ('9001b',"")
Insert Into inter1 (slgno,lots) Values ('9001c',"")
Insert Into inter1 (slgno,lots) Values ('9001d',"")
Insert Into inter1 (slgno,lots) Values ('9002a',"")
Insert Into inter1 (slgno,lots) Values ('9002b',"")
Insert Into inter1 (slgno,lots) Values ('9002c',"")
Insert Into inter1 (slgno,lots) Values ('9003c',"")
Insert Into inter1 (slgno,lots) Values ('9003d',"")
*desired result is a second table
*lots.dbf with 2 fields slgno and lots
*with 3 records
*(slgno)(lots)
* 9001	abcd
* 9002	abc
* 9003  cd

Use Inter1
Set Unique On
Index On Substr(slgno,1,4) Tag lots
Set Order To lots
*we are creating labels for only section 9
Set Filter To Substr(slgno,1,1)=[9]
*put the lots into lots dbf so there is only one record for each lot instead of more
Copy To lots1 Fields slgno,lots
*use and index the new file lots on slgno
Use lots1
Index On slgno Tag slgno
Set Order To slgno
Goto Top

Do While .Not. Eof()
	cLookup=Substr(slgno,1,4)
*create cursor with first lot number in it
	Select * From Inter1 Where Substr(slgno,1,4)=cLookup Order By slgno Into Cursor temp
	Select temp
	cMv=[]
	Scan
		cMv=cMv+Substr(slgno,5,1)
	Endscan
	Select lots1
	Replace lots With cMv
	Skip
Endd

Brow && This is the desired output for reporting

 

[baltman]

SELECT PADR(LEFT(slgno,4),5,CHR(32)) as slgno,;
	PADR(MAX(IIF([a]$slgno,[a],[]))+;
	MAX(IIF([b]$slgno,[b],[]))+;
	MAX(IIF([c]$slgno,[c],[]))+;
	MAX(IIF([d]$slgno,[d],[])),5) as lots ;
	GROUP BY 1 from inter1 ;
	where LEFT(slgno,1)=[9] ;
	INTO CURSOR curResult READWRITE 
	
REPLACE ALL slgno WITH ALLTRIM(slgno)+LEFT(lots,1)
LOCATE 
BROWSE NOWAIT

 

[TamarGranor]

If you can actually have any letters or characters following the number, so Baltman's code won't work for you, I think you'll need a loop, but you might try it like this and see whether it's quicker:

SELECT distinct left(slgno,4) AS SlgNo, ;
       right(slgno,1) as lotno ;
   FROM Inter1 ;
   ORDER BY SlgNo, LotNo ;
   INTO CURSOR Intermediate

cCurSlgNo = SlgNo
cCurLot = ""
SCAN
   IF NOT (cCurSlgNo==SlgNo)
      * Finished this one
      INSERT INTO Lots VALUES (m.cCurSlgNo, m.cCurLot)
   ENDIF

   * Add this lot
   cCurLot = m.cCurLot + LotNo
ENDSCAN

INSERT INTO Lots VALUES (m.cCurSlgNo, m.cCurLot)

This is untested, but should give you the basic idea.

Tamar

 

[white605]

Baltman - after changing the case
from (IIF([a]$slgno,[a],[])
to (IIF([A]$slgno,[A],[]) and so on in your exmple
the desired result was thrown into the cursor - now pass to the report - Thanks

Tamar - yes, a loop will be required, this was similar to my code but I used 2 files first creating a file with only one record for each unique subs(slgno,1,4) in to collect the results in.

Thanks for your replys
wjwjr

 

[TamarGranor]

I suspect mine will be faster because it uses a single query to hone the data down to what you need. Let me know how it works out.

Tamar