0

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!

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129
rock
  • 585
  • 3
  • 10
  • 26

2 Answers2

1

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.

Twelfth
  • 7,070
  • 3
  • 26
  • 34
  • Wow.. that was a very fast reply! So you mean I can just ignore the SET ENGINEBEHAVIOR command and continue to rewrite the other foxpro statements? Thanks so much for the help Jon! – rock Jun 12 '12 at 18:33
1

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.

DRapp
  • 47,638
  • 12
  • 72
  • 142