Microsoft KB Archive/164369

= PRB: View Designer Rewrites WHERE Clause =

Article ID: 164369

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q164369



SYMPTOMS
When a local SQL view is created from a CREATE SQL VIEW command with SELECT - SQL statement, the parenthetical WHERE clause is rewritten in View Designer.



CAUSE
According to the SQL lexicon, there is no parentheses support in SQL.



RESOLUTION
In View Designer, the parenthetical HAVING clause is parsed correctly. Since HAVING sets conditions for the GROUP BY clause similarly to the way that WHERE sets conditions for the SELECT clause; and HAVING without GROUP BY has the same filtering effect as WHERE. The workaround for this problem is to replace WHERE clause with HAVING, whenever it is applicable.



STATUS
This behavior is by design. The view designer was not built to handle parenthetical WHERE clauses.



Steps to Reproduce Behavior
 Open Tastrade sample project and database.  At the Command window, issue the following command: CREATE SQL View Test1 AS ; SELECT * FROM customer ; WHERE (customer.country = "USA" OR customer.country = "Mexico") ; AND max_order_amt > 10000  Open View Designer to modify view Test1.  Select "View SQL" under Query menu. The SQL code is rewritten as the following. Note that the WHERE clause is wrongly parsed: SELECT *; FROM tastrade!customer; WHERE customer.country = "USA"; OR (customer.country = "Mexico";     AND customer.max_order_amt > 10000) 

However, at step 2, if you replace WHERE with HAVING, the View Designer parses the SQL statement correctly.

Keywords: kbprb KB164369

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.