Article ID: 813361 - View products that this article applies to.
This article describes the behavior changes to SQL SELECT statement execution in Microsoft Visual FoxPro 8.0 (VFP8). This article discusses why these changes were made, and how to change your SELECT statements to avoid errors that you may experience because of these behavior changes. This article also contains workarounds for these errors.
Several changes are made to SQL SELECT statement behavior in VFP8 compared to earlier versions of Visual Fox Pro. The changes are designed to prevent ambiguous data that previous versions of Visual FoxPro (VFP) returned. You can configure this behavior. Therefore, you can use Visual FoxPro 7.0 compatibility mode, or Visual FoxPro 8.0 mode. The default mode is 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:
This code runs successfully and returns one customer record per country. This may not return the output that you want because this returns only one customer per country, and you have no control over the customer record that is returned for a particular 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:
SQL: GROUP BY clause is missing or invalid.
This is error 1807.
The following code runs in VFP8 without any errors:
The following is another example:
This SELECT statement returns ambiguous data in earlier VFP versions, (for example, it returns a single region record for each country). This SELECT statement also causes error 1807 in VFP8. To return correct data, you can rewrite this SELECT statement as follows:
If you use a HAVING clause together with a GROUP BY clause, the GROUP BY clause lists every field in the HAVING clause except for fields that are contained in aggregate functions. Any violation of this condition raises the following error message in VFP8:
This is error 1803.
SQL: HAVING clause is invalid.
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.
This SELECT statement is not very effective because there can be more than one region in each country grouping. The following is the corrected code that runs successfully in VFP8, and yields correct results:
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:
This is error 34. To work around this error, wrap the Memo field expression inside a function such as the PADR() function or the ALLTRIM() function. For more information about these functions, see Microsoft Visual FoxPro Help.
Operation is invalid for a Memo, General or Picture field.
Run the following code in both VFP7 and VFP8 versions to verify the change:
In previous VFP versions, the SQL SELECT statement that is described earlier in this article runs without error. However, contents of the Memo and the General fields are ignored. You receive a single record for Title = 'Sales Representative' in the following example, even though there are more such records in the Employee table:
This behavior may lead to unexpected results if the Memo fields are the only non-distinct fields in the SELECT list, as in the earlier case. If there is no key field, and you have multiple Memo field values where the remaining field values are distinct, typically you receive the first or last records of Memo fields corresponding to the other distinct field values.
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 is error 34.
Operation is invalid for a Memo, General or Picture field.
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:
This behavior is related to the SELECT DISTINCT behavior that is discussed earlier in this article.
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:
In VFP7, the SELECT returns a single record with '123'.
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:
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:
SQL SELECT behavior in VFP8To make sure that SQL Select behavior is correct in Visual FoxPro 8.0, follow these steps:
For more information about SQL SELECT statement changes in VFP8, see the following topics in Visual FoxPro 8.0 Help:
"Behavior Changes Since Visual FoxPro 7.0"
"SET ENGINEBEHAVIOR Command"