Reputation: 705
We are having some serious issues migrating Visual FoxPro to SQL Sever..
I don't know much about FoxPro.. Anybody who have done this migration could please help me with my question below
How do I write an equivalent SQL statement to SET ENGINEBEHAVIOR 70 (SET ENGINEBEHAVIOR 80)
found in FoxPro.. What is the purpose of SET ENGINEBEHAVIOR
at all? I read some where that it is to list all fields which are neither part of aggregate functions or group by clause.
Any help is much appreciated!
Upvotes: 0
Views: 271
Reputation: 48179
Twelfth is accurate on how SET ENGINE BEHAVIOR operates. Just note that not all SQL databases support this feature and REQUIRE you to enter all non-aggregate columns whenever you are doing a SQL query.
Say you have 10 records for a customer in an order table and want to count the orders but the table also has an order date, a total amount, an address to deliver to, etc. The person has the different orders delivered to different locations... By doing
select customerID, count(*) as NumberOfOrders, DeliverAddress, TotalOrder
from orders
group by CustomerID
some SQL engines will nag you about not having the "DeliverAddress" or "TotalOrder" as part of the group by. In VFP, these columns will just return the first record found for the customer regardless of how many records were found.
I Believe MySQL also supports this behavior of not REQUIRING the "non-aggregate" columns.
Upvotes: 1
Reputation: 7190
I believe thats a compatibility statement within FOXPro...does a report script contain that statement? It shouldn't be required if you are now running directly on a SQL server...I think it's mostly for Foxpro formatting.
Full info from MSDN is here: http://msdn.microsoft.com/en-us/library/aa978316(v=vs.71).asp
Edit to add: Actually it does appear to affect grouping behaviour. From the link:
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
If you are getting out of FoxPro, I don't think this statement is required anymore.
Upvotes: 1