SQL Data Engine
Visual FoxPro contains a new SET ENGINEBEHAVIOR command so that you can control SQL data engine compatibility. This impacts how SQL SELECT commands function. The current default behavior can be set to previous behavior using the SET ENGINEBEHAVIOR command and is described as follows:

You cannot use the DISTINCT clause with Memo or General fields in a SQL SELECT statement. Instead, wrap a Memo field expression inside a function such as PADR( ) or ALLTRIM( ).
The GROUP BY clause does not support Memo or General fields.
The GROUP BY clause must list every field in the SELECT list, except for fields contained in an aggregate function, such as the COUNT( ) function. In addition, the GROUP BY clause must also list every field in a HAVING clause, except for fields contained in an aggregate function.

A SQL SELECT statement can contain the HAVING clause without the GROUP BY clause, as long as the SQL SELECT statement does not contain any aggregate functions.
SQL SELECT statements do not automatically remove trailing spaces from the values that are compared using the LIKE operation. In versions prior to Visual FoxPro 8.0, both values in the LIKE operation were trimmed from the right prior to evaluation so that the LIKE operation could not possibly report all the correct matches.


Implicit Data Conversion for SQL SELECT...UNION Command
The SQL SELECT command with the UNION keyword now performs implicit data conversion for data types that support implicit conversions. For example, UNION operations with columns of different lengths or that contained different types previously failed unless you explicitly matched the column sizes using a conversion function. Suppose you have the following code:

CREATE CURSOR t1 (f1 c(10))
INSERT INTO t1 VALUES ('1111111111')
CREATE CURSOR t2 (f1 c(2))
INSERT INTO t2 VALUES ('11')
SELECT * FROM t1 INTO CURSOR t3 UNION SELECT * FROM t2 && Fails.
This code failed unless you explicitly match the column sizes as shown:

SELECT * FROM t1 INTO CURSOR t3 UNION SELECT SUBSTR(f1,1,2) FROM t2
You no longer need to specify or use explicit conversion functions for data types that support implicit data conversion.


Inserting Rows from a SQL SELECT Command
You can now insert rows from a SQL SELECT command when you use the SQL INSERT command. You can copy the contents of selected columns or all columns in a row. However, you must make sure the data you copy is compatible with the columns in the rows that you copy to. For more information, see the INSERT - SQL Command.

You can retrieve the number of records processed by the most recently executed table command using the _TALLY system variable, which is updated with the number of rows inserted.


Enables SQL data engine compatibility with Visual FoxPro 7.0 or Visual FoxPro 8.0.

SET ENGINEBEHAVIOR 70 | 80
Parameters
70
Specifies that Visual FoxPro treats SQL SELECT commands as it did in versions prior to Visual FoxPro 8.0
80
Specifies that Visual FoxPro treats SQL SELECT commands as follows. (Default) SQL SELECT clause Behavior

You cannot use the DISTINCT clause with Memo or General fields. Instead, wrap a Memo field inside a function such as PADR( ) or ALLTRIM( ).
For more information, see PADL( ) | PADR( ) | PADC( ) Functions and ALLTRIM( ) Function.

The UNION clause does not support Memo fields unless the ALL clause is included in the SQL SELECT statement.

The GROUP BY clause does not support Memo or General fields.
The GROUP BY clause must list every field in the SELECT list except for fields contained in an aggregate function, such as the COUNT( ) function. In addition, the GROUP BY clause must also list every field in a HAVING clause except for fields contained in an aggregate function.

For example, the following code raises an error because the field company is not listed in the GROUP BY clause.

SELECT company, country FROM Customer GROUP BY country
You can include an aggregate function in the SELECT list without having it in the GROUP BY clause.

For example, the following code uses the COUNT( ) function on the field company without having the field company in the GROUP BY clause.

SELECT COUNT(company), country FROM Customer GROUP BY country


A SQL SELECT statement can contain the HAVING clause without the GROUP BY clause as long as the SQL SELECT statement does not contain any aggregate functions.
For example, the following code filters query results by specifying the country field must equal "Sweden".

SELECT customerid FROM customers HAVING country="Sweden"


SQL SELECT statements do not automatically remove trailing spaces from values compared with the LIKE operation. In versions prior to Visual FoxPro 8.0, both values in the LIKE operation were trimmed from the right prior to evaluation.
For example, the following code assumes that you have a table named table1, and the table has three rows that contain values of "1 ", "12 ", and "123", respectively.

SELECT * FROM table1 WHERE column1 LIKE "1__"
Visual FoxPro version 7.0 and earlier returns one row with the value of "123". Visual FoxPro 8.0 returns three rows with the values, "1 ", "12 ", and "123".

If the beginning of the filter condition matches the pattern of the expression in the LIKE operation, and the rest of the filter condition contains trailing spaces, the LIKE operation ignores the trailing spaces and returns True (.T.). Trailing spaces in the pattern are not ignored.


Remarks
Using SET ENGINEBEHAVIOR set to 70 might lead to ambiguous results from SQL SELECT commands when the DISTINCT and UNION clauses are used with memos and the GROUP BY clause, the HAVING clause without a GROUP BY clause, and when using the LIKE clause.

The scope of SET ENGINEBEHAVIOR is global.