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

Blogs and RSS come to Microsoft.com

MS has just introduced their portal and it's pretty comprehensive. Nothing quite like learning that some people use AIM instead of MSN messenger, or that there really may be a need for supporting 4 monitors ( Cyrus Complains ) However, it's really a great sign that MS is serious about supporting the blogging community which seems to have um, exploded in size in the past year. Blogs and RSS come to Microsoft.com
1 comment
Read more

Elevating Project Specifications with Three Insightful ChatGPT Prompts

For developers and testers, ChatGPT, the freely accessible tool from OpenAI, is game-changing. If you want to learn a new programming language, ask for samples or have it convert your existing code. This can be done in Visual Studio Code (using GitHub CoPilot) or directly in the ChatGPT app or web site.  If you’re a tester, ChatGPT can write a test spec or actual test code (if you use Jest or Cypress) based on existing code, copied and pasted into the input area. But ChatGPT can be of huge value for analysts (whether system or business) who need to validate their needs. There’s often a disconnect between developers and analysts. Analysts complain that developers don’t build what they asked for or ask too many questions. Developers complain that analysts haven’t thought of obvious things. In these situations, ChatGPT can be a great intermediary. At its worst, it forces you to think about and then discount obvious issues. At best, it clarifies the needs into documented requirements. ...
Post a Comment
Read more

Programmers vs. Developers vs. Architects

I received an email this morning from Brandon Savage 's newsletter. Brandon's a PHP guru (works at Mozilla) but his newsletter and books have some great overall perspectives for developers of all languages. However, this last one (What's the difference between developers and architects?) kind of rubs me the wrong way. Either that, or I've just missed the natural inflation of job descriptions. (maybe, it's like the change in terminology between Garbage man and Waste Engineer or Secretary and Office Administrator) So maybe it's just me - but I think there's still a big difference between Programmer, Developer and then of course, architect. The key thing here is that every role has a different perspective and every one of those perspectives has value. The original MSF create roles like Product Manager, Program Manager, Developer, Tester, etc - so every concept may pigeon hole people into different roles. But the statements Brandon makes are often distinction...
12 comments
Read more