Article ID: 813361 - Last Review: January 27, 2005 - Revision: 2.2

SQL SELECT behavior changes in Visual FoxPro 8.0

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

SUMMARY

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.

Back to the top

MORE INFORMATION

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.

Back to the top

Changes to the GROUP BY and HAVING clauses

In 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

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:

SELECT Count(*), Country FROM Customer GROUP BY Country

The following is another example:

SELECT Region, Country FROM Customer GROUP BY Country

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:

SELECT Region, Country FROM Customer GROUP BY Region, Country

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:

SQL: HAVING clause is invalid.

This is error 1803.

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)

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:

SELECT Count(*), Region, Country FROM Customer GROUP BY Country, Region HAVING NOT Empty(Region)

Back to the top

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:

Operation is invalid for a Memo, General or Picture field.

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.

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

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:

SELECT DISTINCT Title, Notes FROM Employee WHERE Title = 'Sales Representative'

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.

Back to the top

SELECT … UNION changes

IN 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:

Operation is invalid for a Memo, General or Picture field.

This is error 34.

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'

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.

Back to the top

SELECT … LIKE changes

In 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 VFP7, the SELECT returns a single record with '123'.
In VFP8, the SELECT returns 3 records, '1', '12', and '123'.

Back to the top

Configuring SQL SELECT behavior

The 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

-or-

SYS(3099,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

-or-

SYS(3099, 80)

SQL SELECT behavior in VFP8

To 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.

Back to the top

REFERENCES

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"

Back to the top