DOCUMENT:Q164369  04-FEB-2000  [foxpro]
TITLE   :PRB: View Designer Rewrites WHERE Clause
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:3.0,3.0b,5.0,6.0
OPER/SYS:
KEYWORDS:kbvfp kbvfp300 kbvfp300b kbvfp500 kbvfp600

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 6.0 
-------------------------------------------------------------------------------

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.


MORE INFORMATION
================

Steps to Reproduce Behavior
---------------------------

1. Open Tastrade sample project and database.

2. 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

3. Open View Designer to modify view Test1.

4. 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.

Additional query words:

======================================================================
Keywords          : kbvfp kbvfp300 kbvfp300b kbvfp500 kbvfp600 
Technology        : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP300b kbVFP500 kbVFP600
Version           : WINDOWS:3.0,3.0b,5.0,6.0
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2000.