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]

Obviously there are the trivial solutions (repeating 50 times INSERT INTO, for the first request), but these not the ones I'm looking for.
I'm looking for solutions that can be expressed with a reasonable number of commands, without a loop. even for 1000 rows (for both requests)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

I don't understand the motivtion for the rule using loops. Do you want to say the code could do without loops and they are allowed to be used to shorten the code, if the same query is executed many times (eg without using a loop counter variable inside the query)?

And about question 1: Only sql SELECT is allowed, so the goal is to have a result cursor with 50 rows, not 50 rows inside cc?

Bye, Olaf.

 

[Olaf Doschke]

I bet you don't have #1 this way...

Spoiler

CREATE CURSOR cc (ii I)
INSERT INTO cc VALUES (1)
INSERT INTO cc VALUES (0)
Select cc.ii+2*dd.ii+4*ee.ii+8*ff.ii+16*gg.ii+32*hh.ii as ii;
from cc, cc as dd, cc as ee, cc as ff, cc as gg, cc as hh ;
into cursor curResult;
Where cc.ii+2*dd.ii+4*ee.ii+8*ff.ii+16*gg.ii+32*hh.ii between 1 and 50;
order by 1

It's also against the rule to only use SELECT, so it's no solution, still worth mentioning, as it's thinking outside of the box.

Bye, Olaf.

 

[vgulielmus]

The answer to your both questions is "yes"; and this is a good solution, although with one more Insert (but still proves the power of SQL).

By using loops I tried to suggest that the solutions can be generalized.
For #2 I found one (iterative) solution using SQL SELECT and SQL UPDATE, with good results even with a large number of records (tested with 1000, 10000, 100000 and 1000000)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

My and your solution for #1, have many points in common, but your is much faster.

Spoiler

You can replace
INSERT INTO cc VALUES (0)
with
SELECT NVL(cc.ii,cc1.ii-1) as ii FROM cc FULL JOIN cc cc1 ON ISNULL(cc.ii+cc1.ii) INTO CURSOR cc
and your solution contains only SELECT

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

My solution for #2 is a sort of divide et impera, using intermediate results (cursors)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

The description of my solution :
Step 1
[pre]
ii cc
1 AAABBB
2 CCCDDD
3 EEEFFF
4 GGGHHH
5 IIIJJJ [/pre]
Step 2
[pre]
ii cc
1 AAABBBCCCDDD
2 EEEFFFGGGHHH
3 IIIJJJ [/pre]
Step 3
[pre]
ii cc
1 AAABBBCCCDDDEEEFFFGGGHHH
2 IIIJJJ [/pre]
Step 4
[pre]
ii cc
1 AAABBBCCCDDDEEEFFFGGGHHHIIIJJJ [/pre]

The cursors showed in step 1 and step 2 are different, and are generated with the help of Select and Updates

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

Another take on #1 in the same manner as in SQL Server with a solution called number or tally table, even without cursor cc:

Spoiler

* Common practice in T-SQL is to derive such tables from a large seed table such as master.dbo.syscolumns.
* Since it's present in any server an has plenty records, a cross product with itself is large enough for most
* cases and row_number() can be used to get a simple number sequence table.
* There is no such system table in VFP, the only thing you may have automatic is foxuser.dbf, but you may
* take any large table of your apps database, too:
Select TOP 50 aa.jj+Reccount()*bb.kk+1 as ii FROM;
(Select Recno()-1 as jj FROM (Sys(2005))) aa;
full join;
(Select Recno()-1 as kk FROM (Sys(2005))) bb;
on .t.;
Into Cursor curResult;
Order by 1

Bye, Olaf.

 

[Olaf Doschke]

Since you post your steps of #2 I get this idea:

Spoiler

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

Do While Reccount("cc")>1
Update cc Set cc=Alltrim(cc.cc)+Alltrim(next.cc) from cc inner join cc as next on next.ii=cc.ii+1 Where cc.ii%2=1
Select (ii+1)/2 as ii, cc from cc Where ii%2=1 Into Cursor cc Readwrite
Enddo

Bye, Olaf.

 

[vgulielmus]

Great !
I wait for other solutions.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

#1 once more with just a SELECT:

Spoiler

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

Select b0.bit+b1.bit+b2.bit+b3.bit+b4.bit+b5.bit as ii From;
(Select ii as bit from cc;
union;
Select ii-1 from cc) b0,;
(Select 2*ii as bit from cc;
union;
Select ii-1 from cc) b1,;
(Select 4*ii as bit from cc;
union;
Select ii-1 from cc) b2,;
(Select 8*ii as bit from cc;
union;
Select ii-1 from cc) b3,;
(Select 16*ii as bit from cc;
union;
Select ii-1 from cc) b4,;
(Select 32*ii as bit from cc;
union;
Select ii-1 from cc) b5;
Where b0.bit+b1.bit+b2.bit+b3.bit+b4.bit+b5.bit between 1 and 50;
order by 1

It's a bit cumbersome to go this route, though.

Bye, Olaf.

 

[Olaf Doschke]

And finally...

Spoiler

*I'd use this, perhaps
CREATE CURSOR cc (ii I)
INSERT INTO cc VALUES (1)

Do While Reccount("cc")<50
Append From Dbf("cc")
EndDo
Delete For Recno()>50
Replace All ii with Recno()
Browse

Bye, Olaf.

 

[vgulielmus]

Awesome!
I hoped to see more participants...

Maybe this week I wasn't too inspired
Because these exercises make me feel good, I will participate gladly to others proposals.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

I think this exercise was interesting and challenging enough.
So let's wait for monday.

Bye, Olaf.

 

[Mike Lewis]

I hoped to see more participants...

Don't be put off by that. The only reason I didn't take part myself was because I have been very busy these last couple of days. But it has still been interesting for me to read the discussion.

By the way, Vilhelm-Ion, could I make two small suggestions. First, add a sequence number to the thread titles. Instead of just "Something for the weekend", call them "Something for the weekend #10" or whatever. (My own threads got up to #6, so on that basis this one would be #8.) Also, for the same reason, append a short description to the title. For example, this one might be "Something for the weekend #8: The power of SQL". That way, anyone looking down the list of threads will see which is which.

I hope you don't mind me making these suggestions. In any case, I'm glad you've been starting these discussions, especially as I have run out of ideas recently.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Another solution to #2:

Spoiler

Create Cursor cc (ii I Autoinc,cc C(100))
For lni = 1 To 10
Insert Into cc (cc) Values (Replicate(Chr(64+lni),3))
Next

* Instead of update use replace, instead of a self join do a relation based on recno()+1 to relate cc to the next record:
Do While Reccount("cc")>1
Use In Select("dd")
Use Dbf("cc") In 0 Again Alias dd
Select cc
Set Relation To Recno()+1 Into dd
Replace All cc With Alltrim(cc.cc)+Alltrim(dd.cc) In cc For cc.ii%2=1
Select (ii+1)/2 As ii, cc From cc Where ii%2=1 Into Cursor cc Readwrite
Enddo
* something similar is possible in T-SQL since 2012 with LEAD and LAG functions but the physical nature of DBFs it was always possible with DBFs since RELATION.

Bye, Olaf.

 

[vgulielmus]

Mike, thank you for the kind words and the suggestions
Me too, am little a busy this weekend, because I made a promise and I try to honor it.

Very interesting new solution you found, Olaf!
Yours are better (shorter and faster) than my initial solutions


Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

In regard to the number of the puzzles I think I numbered mine as #7, so we should be at #9 or #10 already.

Bye, Olaf.

 

[Mike Lewis]

You're right, Olaf. This one should be #9.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads