Skip to main content

VFP 9: Major SQL SELECT Enhancements

Visual FoxPro 9 includes several SQL improvements especially on the SELECT side of things. It's got neat features that already were available in SQL Server like CAST and VarChar data types but some of the fun are on the limitations that have been removed.
 
In VFP 8 and earlier, you could only do 9 joins in a SELECT statement and only with a maximum of 30 tables. With VFP 9, you're unlimited.
 
You can reference more than 30 tables or aliases in a single SELECT statement and join them as much as you want.
 
Smaller applications may not need this but as you come across more and more normalized databases, removing this limitation is huge.
 
But what's more is the other limitations that have been removed from the SQL - SELECT statement.
 
- unlimited UNION statements
- you can specify more than 24 items in an IN statement. SELECT * FROM customers where state in ("NC","AL",etc, etc). In previous versions, you had a maximum of 24. Even though this approach would call out for a better criteria, if you are allowing your users to build complex queries, this removes a lot of "why can't I? " type questions.
 
 
Perhaps even cooler is how VFP 9 really supports subqueries. Consider the following statement:
 
SELECT ordid from (select * from ordhist) as x

In previous versions, you might have to do :

SELECT * from ORDHIST where <condition> into cursor x

SELECT ordid from x

Now you can put it all into a single line.

You can also use subqueries as a line item:

SELECT custname,custno, (SELECT sum(totchgs) From frmast) as ntotal,sum(totchgs),sum(totchgs)/(SELECT sum(totchgs) From frmast)*100 as nperc From frmast GROUP BY 1,2

This query shows the customer name, the total of all invoices (in the file), the total invoices for that customer and then the percentage of total invoices that the customer held all in one query.

In earlier versions, this would be done with multiple lines but now you can do it all in one.

All of these changes are going to be a great reason to refactor your older code. One of the joys I always get is trying to reduce the size of my application. With the new SQL enhancements in Europa, it's going to be lots of fun!

Comments

Garrett said…
One very good reason for removing the limit on the IN clause is that GIS software may do queries using this syntax, which used to make it difficult to use Fox with them. I was very happy to see this change....
June 28, 2004 at 10:59 AM
Post a Comment

Popular posts from this blog

Who Wants To Be Logical? I Do! I Don't! (with Claude)

In my book, Towards Consciousness, I explore PET , a theory that attempts to explain consciousness and possibly how it might apply to AI. As part of the conversations with AI series, I've included this conversation with Claude.AI in which we were talking about something very different and yet the conversation redirected itself back to PET. For the record, there is no such thing as "Colfsity". A It's interesting to me that people want to proclaim their distinction from being rigid and unlike machines, yet they also complain that the "best" people keep a rigid schedule, regular routines and logic understanding of issues. That's a perceptive observation about a real tension in how people think about themselves and what they value. There does seem to be this paradox where rigidity is simultaneously rejected as "robotic" or inhuman, yet praised when it appears as discipline, consistency, or logical thinking. I think part of what's happening is t...
Post a Comment
Read more

Merry Christmas

Whether you celebrate now, have already celebrated your past holiday, are planning to, or not, I hope you find yourself surrounded by family and those you love during this holiday season and are able to remember fondly those who are not with you. We often find ourselves buried in work, or too distracted by other problems, to share and remember a few moments with those closest to us. So many of us are separated by huge distances, we often find it easier to focus on those who are out of arm's reach yet so easy to connect with online, instead of those who are right beside us. So do yourself a favour, put down the keyboard (stylus, mouse, etc) and spend that time. I know I will. Merry Christmas. Powered by ScribeFire .
4 comments
Read more

Comparing education systems based on their technology

(yes, I originally found this blog because of Scoble's note on it BUT it was kind of interesting all the same) Read Alex Mallet's summary of his first week at MIT. Malletrivia: Summary of the first complete week Wow - "There's an incredible amount of material packed into each lecture" - and it sounds interesting... Compared to some of the lecture content I have seen at our universities, you really must get what you pay for. While it sounds like Alex is writing frantically down notes in his classes, at least he's finding something worth writing about in them. Case in point: one of our local universities ( Carleton ) puts some lecture classes on the TV (like many universities do) - but you would think they purposely find the most boring professors to teach them. They put up PowerPoint slides with 30 points on them, speak in monotone voice (yes, imagine the typical caricature of the university lecture from years ago), tell the students to print their...
8 comments
Read more