SQL SELECT in VFP and T-SQL

Watch out for these language differences when moving between Visual FoxPro and SQL Server.

Over the last few years, the FoxPro team at Microsoft has done an excellent job of bringing VFP's SQL sub-language closer to accepted industry standards. In particular, they have made the SELECT statement more closely conform to ANSI-92 standards, which is good news for developers who need to move between VFP and back-end databases like SQL Server and Oracle.

Yet VFP's version of SELECT still has enough syntax and other differences to trap the unwary programmer. Some of these differences reflect the peculiarities of the FoxPro language, while others offer genuinely useful - albeit non-standard - services that developers would be reluctant to do without - the ability to send query results direct to an array, for example.

In this article, we highlight the differences between the VFP and SQL Server (T-SQL) versions of SELECT. We originally compiled the list with SQL Server 2000 in mind, but all of the points apply equally to SQL Server 2005 (as well as to version 7). If you know of any differences not covered here, we'd welcome your feedback.

Order of clauses

VFP has always been relaxed about the order of the clauses within a statement, and that applies equally to SELECT. Put your ORDER BY before the FROM clause, or your TOP N right at the end of the command, and VFP won't so much as blink. T-SQL is much stricter. In general, T-SQL demands that the clauses appear in the order in which they are listed in the syntax diagram in the Help file.

Empty column names

An oddity of T-SQL that sometimes surprises VFP folk is its ability to generate result sets with empty column names. This happens by default with calculated or aggregate columns. For example, in the results of this query:

SELECT EmployeeID, UPPER(LEFT(LastName,4)) 
  FROM Employees

the second column will have no name. In VFP, the column name would default to something like Exp_1.

In both versions, you can use the AS clause to assign a name to a column:

SELECT EmployeeID, 
  UPPER(LEFT(LastName,4)) AS ShortName 
  FROM Employees

but T-SQL gives you an alternative syntax that's not supported by VFP:

SELECT EmployeeID, 
  ShortName = UPPER(LEFT(LastName,4)) 
  FROM Employees

TOP N

In VFP, SELECT statements with a TOP N clause must also have an ORDER BY clause. Not so in T-SQL. You may omit the ORDER BY, in which case the ordering will be arbitrary.

WITH TIES

In T-SQL, you can add WITH TIES to a TOP N clause to indicate that the result set should include any tied values in the Nth row. So, if you specify TOP 10 WITH TIES, and the 10th and 11th rows have the same value, you'll get 11 rows in the result set.

VFP doesn't support this clause. However, VFP 8.0 and below always behave as if WITH TIES was present. So, the following query will produce 11 rows if the 10th and 11th rows have the same order amount:

SELECT TOP 10 Order_Amt FROM Orders ;
  ORDER BY Order_amt

If that's not what you want, add a GROUP BY clause, like so:

SELECT TOP 10 Order_Amt FROM Orders ;
  ORDER BY Order_amt GROUP BY Order_Amt

In VFP 9.0, the default behaviour is not to return tied values. So a query will always return exactly the number of rows specified in the TOP N clause. You can make VFP 9.0 behave like earlier versions by setting ENGINEBEHAVIOR to 70 or 80, but this will be at the expense of performance. (Our thanks to Stella Levin for pointing out this behaviour to us and for suggesting the GROUP BY workaround shown here.)

Optional FROM clause

Now here's another one that surprises a lot of people. In T-SQL, it's legal to omit the FROM clause from a SELECT command - but only if the expressions in the expression list don't reference any columns in a table. Doing so will always generate a result set with exactly one row. The following command, for instance, would produce a result set containing just the current datetime:

SELECT GETDATE() AS Current

VFP would unhesitatingly reject a command like that.

LIKE operator

VFP and T-SQL both let you use wildcard characters ('_' and '%') with the LIKE operator. These represent a single character and an arbitrary number of characters respectively. So this query:

SELECT * FROM Products 
  WHERE Product_Name LIKE '%chocolate%'

will find all products whose names includes 'chocolate'.

T-SQL goes one better by letting you also use square brackets to denote ranges and sets. For example, the following statement would find products whose codes start with A, B or C, followed by two digits:

SELECT * FROM Products 
  WHERE Product_Code LIKE '[A-C][0-9][0-9]'

Unfortunately, this handy feature is not available in VFP.

GROUP BY and aggregate functions

This one has trapped many VFP developers, especially since VFP 8.0 first appeared. In earlier versions, the following syntax was legal:

SELECT Country, City, AVG(Inv_Total) ;
  FROM Invoices GROUP BY Country

However, although legal, that syntax produced potentially incorrect results. In the above example, VFP would select an arbitrary city to match each country, even though a given country could have many cities.

ASNI-92 SQL imposes a simple rule: if a query has a GROUP BY clause, every item in the expression list (the list immediately following the keyword SELECT) must either be an aggregate function or one of the columns listed in the GROUP BY clause. T-SQL strictly enforces that rule, as does VFP 8.0 and above, but earlier versions of FoxPro don't (in VFP 8.0 and above, you can override the enforcement by setting ENGINEBEHAVIOR to 70).

Grouping on column numbers

Still on the subject of GROUP BY, the following syntax is legal in all versions of VFP:

SELECT Country, AVG(Inv_Total) ; 
  FROM Invoices GROUP BY 1

T-SQL would choke on that because column numbers are not permitted in the GROUP BY clause. You would have to use the following form (which would also be legal in VFP):

SELECT Country, AVG(Inv_Total) 
  FROM Invoices GROUP BY Country

However, both versions allow the use of column numbers with ORDER BY.

HAVING clause

In VFP, you can use a HAVING clause even if there is no GROUP BY clause. In that case, HAVING behaves like WHERE. In T-SQL, HAVING is not permitted without GROUP BY.

Outer joins

In T-SQL, you can't specify a join simply as OUTER. Unlike in VFP, you must include the appropriate keyword: LEFT, RIGHT or FULL.

Projections and derived tables

VFP and T-SQL both support subqueries in the WHERE clause. In VFP 9.0, as in T-SQL, subqueries can also appear in the expression list and in the FROM and JOIN clauses. Subqueries in the expression list are called projections, while those in the FROM or JOIN clauses are called derived tables. The Help files for VFP 9.0 and SQL Server both contain examples of how these can be used.

VFP 8.0 and below do not support projections or derived tables.

Query destinations

VFP allows you to send query results to a variety of destinations. By default, they go to a behinds-the-scenes cursor which VFP immediately displays in a Browse window. You can use the PREFERENCE clause to specify options for that window. Alternatively, you can use the TO clause to send the results to the screen, a printer or a text file. Or you can use INTO to place the results in a cursor, table or array.

In T-SQL, query results are simply returned to the client process in the form of a result set. The only option for re-directing them is to send them to a new table, which you do like so:

SELECT * INTO Choc_Products FROM Products 
  WHERE Product_Name LIKE '%chocolate%'

Unlike in VFP, the position of the INTO clause is significant - it must appear between the expression list and the FROM clause - and it shouldn't include the keyword TABLE.

If you want to send the query results to a temporary table - similar to a cursor in VFP - use the same syntax as above, but start the table name with #. Tables whose names begin with # are stored in SQL Server's Tempdb database, and are destroyed when the connections which created them are closed.

Memo and general fields in DISTINCT clause

VFP 7.0 and below allow you to specify memo and general fields in the expression list of a query that includes the keyword DISTINCT, even though such queries produce incorrect results. For VFP 8.0, the FoxPro team tightened up the syntax. Now, memo and general fields are disallowed in DISTINCT queries, unless ENGINEBEHAVIOR is set to 70.

The SQL Server equivalents of memo and general fields are text and image columns. T-SQL does not allow these column types in the result sets of queries that have a DISTINCT clause.

Number of tables

VFP 8.0 and below imposes a limit of 30 tables (to be precise, 30 aliases) in a SELECT statement. The equivalent figure in T-SQL is 256. VFP 9.0 has no theoretical limit.

Nesting of subqueries

In VFP 8.0 and below, you can only have a single level of subquery nesting. In VFP 9.0, there is no theoretical limit. In T-SQL, the limit is 32 levels.

Null values

If you want to exclude null values from a result set (or exclude everything except null values), you can (and should) use IS [NOT] NULL in the WHERE clause. That applies in both VFP and T-SQL.

But be careful about the seemingly similar ISNULL() function. In VFP, this function does what its name suggests: it tells you if a given expression evaluates to null. In T-SQL, ISNULL() is more like VFP's NVL() function. It takes two arguments; if the first argument is null, the function returns the second argument, otherwise it returns the first.

Mike Lewis Consultants Ltd. January 2006.

FOXSTUFF