SQL SELECT behavior changes in Visual FoxPro 8.0
The following sections outline the changes in the various clauses of SELECT statements.
Changes to the GROUP BY and HAVING clausesIn VFP8, the GROUP BY clause lists all the fields in the SQL SELECT statement fields list, except for those fields that are contained in aggregate functions.
In previous versions of VFP, you may not receive the output that you want if the GROUP BY clause does not contain all the fields that are mentioned in the SQL SELECT statement except for fields contained in aggregate functions.
Consider the following example:
A Customer table contains information about customers. This table contains information such as Customer ID, Company, Address, Region, and Country. There can be more than one customer for a particular Region or Country. In the earlier versions of VFP, if you try to select all the customer information that is grouped by country, the result set returns only one customer record per country. This record is typically the last customer record in the order of insertion.
Run the following query in the previous versions of VFP:
OPEN DATABASE Home() + 'Samples\Data\Testdata'SELECT * FROM Customer GROUP BY Country
In VFP8, this type of SELECT statement causes an error. If you run this SELECT statement in VFP8, you receive the following error message:
This is error 1807.
The following code runs in VFP8 without any errors:
SELECT Count(*), Country FROM Customer GROUP BY Country
SELECT Region, Country FROM Customer GROUP BY Country
SELECT Region, Country FROM Customer GROUP BY Region, Country
Note If you do not use a GROUP BY clause, and use only a HAVING clause, this condition does not apply. In the later case, the HAVING clause behaves the same as a WHERE clause, as long as the SQL SELECT statement does not contain any aggregate functions
The following code uses a HAVING clause, and generates an error in VFP8. This code runs successfully in previous versions of VFP, however, it may not give you the output that you want.
SELECT Count(*), Region, Country FROM Customer GROUP BY Country HAVING NOT Empty(Region)
SELECT Count(*), Region, Country FROM Customer GROUP BY Country, Region HAVING NOT Empty(Region)
Changes to the DISTINCT clause
In VFP8, you cannot use the DISTINCT clause with Memo or General fields in a SQL SELECT statement. If you run a SELECT DISTINCT… clause when your field list contains one or more Memo or General fields, you receive the following error message:
Run the following code in both VFP7 and VFP8 versions to verify the change:
OPEN DATABASE Home() + 'Samples\Data\Testdata'SELECT DISTINCT Title, Notes FROM Employee
SELECT DISTINCT Title, Notes FROM Employee WHERE Title = 'Sales Representative'
SELECT … UNION changesIN VFP8, you cannot use a SELECT … UNION SELECT clause with Memo or General fields in a SQL SELECT statement. If you execute a SELECT … UNION SELECT clause when your field list contains one or more Memo or General fields, you receive the following error message:
This occurs because the UNION clause adds an implicit DISTINCT clause. To work around this error, you can use the UNION ALL clause instead of the UNION clause.
Run the following code in VFP7 and VFP8 to demonstrate this change:
OPEN DATABASE Home()+'Samples\Data\Testdata'SELECT Emp_id, Notes FROM Employee WHERE Title = 'Sales Representative' UNION SELECT Emp_id, Notes FROM Employee WHERE Title = 'Sales Manager'
In previous VFP versions, the earlier mentioned SELECT statement runs without error, but contents of any Memo or General fields are ignored. This behavior may lead to unexpected results if the only non-distinct field or fields in the SELECT list are Memo fields. If there is no key field, and you have multiple Memo field values where the remaining field values are distinct, you typically receive the first or last records of Memo fields that correspond to the other distinct field values.
SELECT … LIKE changesIn VFP7 and previous versions, the "_" wildcard character in a SELECT … LIKE clause is not correctly implemented. The "_" wildcard character is not used to compare a blank. This problem is fixed in VFP8.
Run the following code in both VFP7 and VFP8 to verify the behavior:
OPEN DATABASE Home() + 'Samples\Data\Testdata'CREATE TABLE LikeTest (Field1 Char(5), Field2 Char(15))INSERT Into LikeTest VALUES ('1', sys(2015))INSERT Into LikeTest VALUES ('12 ', sys(2015))INSERT Into LikeTest VALUES ('123', sys(2015))INSERT Into LikeTest VALUES ('1234', sys(2015))INSERT Into LikeTest VALUES ('234', sys(2015))SELECT Field1 FROM LikeTest WHERE Field1 LIKE '1__'
In VFP8, the SELECT returns 3 records, '1', '12', and '123'.
Configuring SQL SELECT behaviorThe errors that occur in VFP8 for various SQL SELECT statements are to help to make sure that the data that is returned is correct. You can run any of the SELECT statements that are discussed earlier in this article (that run without errors in earlier versions of Visual FoxPro) in VFP8 without errors by using VFP7 compatibility mode.
You can configure VFP7 compatibility mode by changing the SQL Data Engine behavior. Use the following to configure VFP8 to work in VFP7 mode.
Run any of the following commands in VFP8:
SET ENGINEBEHAVIOR 70
Note As discussed in the earlier sections, view each SELECT statement to make sure that you receive the correct result sets if you decide to run SELECT statements in VFP7 compatibility mode.
To revert to VFP8 mode, run any of the following commands in VFP8:
SET ENGINEBEHAVIOR 80
SQL SELECT behavior in VFP8To make sure that SQL Select behavior is correct in Visual FoxPro 8.0, follow these steps:
- View each SELECT statement that causes errors, and determine which clause caused the error (Verify GROUP BY, DISTINCT, UNION or LIKE).
- Change the SELECT statements wherever possible to avoid changing the ENGINEBEHAVIOR to VFP7 compatibility mode.
- Verify that you receive the correct data that you expect from any SELECT statement that causes the errors and require using SET ENGINEBEHAVIOR 70.
- You can set ENGINEBEHAVIOR globally. To do this, use ENGINEBEHAVIOR=<value> in your config file Config.fpw.
"Behavior Changes Since Visual FoxPro 7.0"
"SET ENGINEBEHAVIOR Command"
Article ID: 813361 - Last Review: 12/08/2015 01:55:57 - Revision: 2.2
- kbnosurvey kbarchive kbmsg kbdatabase kbinfo KB813361