Something for the weekend 2

[vgulielmus]

This weekend my intention is to show the power of VFP's SQL
Again, these exercises are for fun only

a) Given this cursor

CREATE CURSOR cc (ii I)
INSERT INTO cc VALUES (1)

using only SQL SELECT, expand its values to the first 50 integers, e.g
ii
1
2
...
50

b) Given this cursor

CREATE CURSOR cc (ii I AUTOINC,cc C(10))
FOR lni = 1 TO 10
	INSERT INTO cc (cc) VALUES (REPLICATE(CHR(64+lni),3))
NEXT

e.g.
[pre]ii cc
1 AAA
2 BBB
...
10 JJJ[/pre]

using only SQL SELECT and SQL UPDATE, create a cursor cc2 like this one
[pre]ii cc
1 AAABBBCCCDDDEEEFFFGGGHHHIIIJJJ[/pre]
(like STRING_AGG in Postgress or GROUP_CONCAT in MySQL)
A good explanation

If the generalised solution requires a FOR / WHILE loop, you can use the loop.
But for these particular requests, the commands can be repeated explicitly (with copy / paste)


Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

My initial solutions (the generalzed versions)
As I previously mentioned, Olaf Doschke has provided better solutions, more compact and faster than mine.
1) Create a cursor with 1,2,3,...
Subsequently I tried to solve the following problem :
Given a table with a single row, generate a result with more than a single row using only SQL SELECT

CREATE CURSOR cc (ii I)
INSERT INTO cc VALUES (1)
* Total number of rows
nn = 50
* Get the power of 2
FOR lni = 31 TO 0 STEP -1
	IF BITTEST(m.nn, m.lni)
		npow = m.lni
		EXIT
	ENDIF
NEXT
FOR lni = m.npow - 1 TO 0 STEP -1
	IF BITTEST(m.nn, m.lni)
		npow = m.npow + 1
		EXIT
	ENDIF
NEXT
* Generate the result
FOR lni = 1 TO m.npow
	SELECT COUNT(*) as ii FROM cc INTO CURSOR ccount
	SELECT NVL(cc.ii,ccount.ii+cc1.ii) as ii FROM cc FULL JOIN cc cc1 ON ISNULL(cc.ii+cc1.ii) WHERE NVL(cc.ii,ccount.ii+cc1.ii)<= m.nn INTO CURSOR cc
NEXT
BROWSE

The number of iterations is small.
[pre]nn iterations
50 6
100 7
1000 10
10000 14[/pre]
But the algorithm is slow, due to Cartesian product.

2) A simplified version of string_agg
The number of iterations is similar.
[pre]nn iterations
50 3
100 4
1000 5
10000 7[/pre]
Because at one iteration includes two steps.
The algorithm is fast.
For nn = 100000 is performed almost instantaneously
For nn = 1000000 is performed in several seconds

CREATE CURSOR cc (ii I AUTOINC,cc C(10))

* Total number of rows
nn = 50
* Get the power of 2
FOR lni = 31 TO 0 STEP -1
	IF BITTEST(m.nn, m.lni)
		npow = m.lni
		EXIT
	ENDIF
NEXT
FOR lni = m.npow - 1 TO 0 STEP -1
	IF BITTEST(m.nn, m.lni)
		npow = m.npow + 1
		EXIT
	ENDIF
NEXT

* Populate the cursor
FOR lnj = 1 TO FLOOR(nn / 10)
	FOR lni = 1 TO 10
		INSERT INTO cc (cc) VALUES (REPLICATE(CHR(64+lni),3))
	NEXT
NEXT

* First selections, generate the temporary cursors
SELECT ii,CAST(cc as M) as cc FROM cc WHERE ii % 2 = 1 INTO CURSOR cc1 READWRITE 
UPDATE cc1 SET ii = RECNO(),cc = ALLTRIM(cc1.cc) + ALLTRIM(cc.cc) from cc WHERE cc1.ii + 1 = cc.ii
UPDATE cc1 SET ii = RECNO() WHERE cc1.ii > (select MAX(ii) from cc WHERE ii % 2 = 0)

SELECT ii,CAST(cc as M) as cc FROM cc1 WHERE ii % 2 = 1 INTO CURSOR cc2 READWRITE 
UPDATE cc2 SET ii = RECNO(),cc = ALLTRIM(cc2.cc) + ALLTRIM(cc1.cc) from cc1 WHERE cc2.ii + 1 = cc1.ii
UPDATE cc2 SET ii = RECNO() WHERE cc2.ii > (select MAX(ii) from cc1 WHERE ii % 2 = 0)

* Number of iterations = half of the power of 2
FOR lni = 1 TO CEILING(m.npow / 2)
	SELECT ii,CAST(cc as M) as cc FROM cc2 WHERE ii % 2 = 1 INTO CURSOR cc1 READWRITE 
	UPDATE cc1 SET ii = RECNO(),cc = ALLTRIM(cc1.cc) + ALLTRIM(cc2.cc) from cc2 WHERE cc1.ii + 1 = cc2.ii
	UPDATE cc1 SET ii = RECNO() WHERE cc1.ii > (select MAX(ii) from cc2 WHERE ii % 2 = 0)

	SELECT ii,CAST(cc as M) as cc FROM cc1 WHERE ii % 2 = 1 INTO CURSOR cc2 READWRITE 
	UPDATE cc2 SET ii = RECNO(),cc = ALLTRIM(cc2.cc) + ALLTRIM(cc1.cc) from cc1 WHERE cc2.ii + 1 = cc1.ii
	UPDATE cc2 SET ii = RECNO() WHERE cc2.ii > (select MAX(ii) from cc1 WHERE ii % 2 = 0)
NEXT

SELECT cc2
brow

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania