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.
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)
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.
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.
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'.
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.