DOCUMENT:Q138677  27-MAR-2001  [foxpro]
TITLE   :BUG: CREATE SQL VIEW Scrambles the Parentheses in SQL Command
PRODUCT :Microsoft FoxPro
PROD/VER::3.0,3.0b,5.0,5.0a,6.0
OPER/SYS:
KEYWORDS:kbvfp kbvfp300bBUG kbvfp500aBUG kbvfp500bug kbvfp600bug kbDSupportkbbuglist

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

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

SYMPTOMS
========

If, in a CREATE SQL VIEW command, there are parentheses and a logical OR
expression, once the view is added to the database (.dbc file), the parentheses
will appear to be moved.

CAUSE
=====

The parser for the view will group the OR statements until it reaches another OR
or the end of the statement. It has to do this in order to look correctly in the
Selection Criteria of the View Designer.

RESOLUTION
==========

To work around this problem, write the SQL statement without any parenthesis, or
if the SQL statement is to complex, break up the statement with the UNION or
UNION ALL clauses.

STATUS
======

Microsoft has confirmed this to be a problem in the Microsoft products listed at
the beginning of this article.

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

Steps to Reproduce Problem
--------------------------

Create a program with the following code, with the Tastrade.dbc database file
open:

      CREATE SQL VIEW test AS SELECT * FROM customer ;
         WHERE customer.city='London' ;
         AND (customer.country='UK'  ;
         OR customer.postal_code='EC23NT')

This will create a view called Test. If the Test view is modified and on the
Query menu, you click View SQL, you'll see that the generated SQL statement
looks like this:

    SELECT *  ;
     FROM tastrade!customer;
     WHERE Customer.city = "London";
       AND Customer.country = "UK";
       OR (Customer.postal_code = "EC23NT")

Additional query words:

======================================================================
Keywords          : kbvfp kbvfp300bBUG kbvfp500aBUG kbvfp500bug kbvfp600bug kbDSupport kbbuglist
Technology        : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP500a
Version           : :3.0,3.0b,5.0,5.0a,6.0
Issue type        : kbbug
Solution Type     : kbnofix

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

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