In keeping with our commitment to provide you, the user, with support
that will maximize your productivity with the dBASE IV product, we are
continuing our tradition of publishing timely, detailed anomaly and 
work-around reports.

The following listing addresses three known anomalies when using
dBASE IV SQL commands. Use of these techniques will aid you in
avoiding anomalies that have been reported to our Software Support
Center.  This report will be supplemented as new information is
received.

*******************************************************************

The following queries either fail with an "Internal Error" when
executed by dBASE IV's SQL, or give incomplete results.  Three
distinct anomalies are responsible for the seven failing queries:


a)   If the data in a table is more than 95% physically sorted on a
     field,
          BUT less than 100% sorted,
          AND that field is indexed

     the SQL optimizer will assume that the field is, in fact,
     100% sorted.  SQL queries against that field will miss the
     records which are out of order.  

The most likely ways for a user to get a data file "almost in order"
     are:
     
     - the user begins using an application for the first time,
     and enters a large quantity of data in sorted order (such as
     typing in an address book).  The user then starts adding,
     deleting, and changing records.  While the number of changed
     records is less than 5% of the database, the anomaly can
     occur if the critical field is also indexed.
     
     - the user has an application which uses the dBASE SORT
     command to generate a physically sorted file for reporting
     purposes.  The application also updates the file.  The user then
     adds an SQL "addition" to the existing dBASE application.
     
EXAMPLES:

	1. SELECT DISTINCT CLIENTS.ACCTNUM, NAME FROM CLIENTS,
		TRANSACT
		   WHERE CLIENTS.ACCTNUM = TRANSACT.ACCTNUM
		      AND SYMBOL IN 
		          (SELECT SYMBOL FROM TRANSACT
		            WHERE ACCTNUM=1002);

	2. SELECT DISTINCT CLIENTS.ACCTNUM, NAME FROM CLIENTS,
		TRANSACT
		   WHERE CLIENTS.ACCTNUM = TRANSACT.ACCTNUM
		      AND SYMBOL IN 
		          (SELECT SYMBOL FROM TRANSACT
		            WHERE ACCTNUM=1002)
			      AND ACCTNUM = 1144;

     Note 1: This query returns an "Ambiguous column name" error since
     the column "ACCTNUM" is in both tables.  The last line in example
     2 should read "AND CLIENTS.ACCTNUM = 1144".
     

         3. SELECT distinct NAME FROM CLIENTS
     	      WHERE "GM" IN
        	(SELECT trim(SYMBOL) FROM TRANSACT
     		   WHERE ACCTNUM = CLIENTS.ACCTNUM);

     Note 2: One needs to add the "DISTINCT" keyword and the
     "TRIM()" function indicated above in lower case to get the
     right results.  

          4. SELECT ACCTNUM, COUNT(*) FROM TRANSACT
               GROUP BY ACCTNUM
     	       HAVING COUNT(*) > 60;

     WORK-AROUND:
     
     Observe that all four queries involve selections involving the
     column TRANSACT.ACCTNUM which is also indexed.  This is the field
     which is "almost in order".  The work-around is to drop the index
     on the field.
     

b)   If a query contains an existential predicate ("WHERE EXISTS..."),
         AND contains a nested subquery,
             WHICH has a local predicate,
             AND WHICH is against a table of more than 5,000 records,

     then the user MAY get an "Internal Error".  The error is
     handled normally by dBASE IV's error trapping.  

EXAMPLES:

     1. SELECT NAME FROM CLIENTS
        WHERE NOT EXISTS
          (SELECT * FROM TRANSACT
             WHERE ACCTNUM = CLIENTS.ACCTNUM
                   AND SYMBOL = "GM");

     2. SELECT NAME FROM CLIENTS
        WHERE EXISTS
          (SELECT * FROM TRANSACT
             WHERE ACCTNUM = CLIENTS.ACCTNUM
                   AND SYMBOL = "GM");

     Note the presence of "EXISTS" or "NOT EXISTS", they make the
     predicate existential.  Also note the nested subquery
     "(SELECT * ..." and the local predicate "AND SYMBOL='GM', a
     predicate which is confined, or local, to the table     
     referenced in the subquery.  If the above conditions exist and
     Also, if the table in the subquery, TRANSACT has more than 5,000,
     the anomaly may occur (it won't always).
     
WORK-AROUND:

     Split the query into two queries, storing intermediate
     results in a temporary table.

     
     SELECT * FROM TRANSACT 
       WHERE "GM" = trim(SYMBOL) SAVE TO TEMP T1;
     SELECT NAME FROM CLIENTS 
       WHERE EXISTS
        (SELECT * FROM T1 WHERE ACCTNUM = CLIENTS.ACCTNUM);

     
c)   If a query contains a two-table join,
        AND a local predicate on the join column,
        AND both join columns are indexed,

     then the user MAY get an "Internal Error".  

EXAMPLE:

     SET SQL ON
     START DATABASE SAMPLES;
     DROP INDEX ORDER_ID;
     DBDEFINE TRANSACT;
     DBDEFINE STOCK;
     CREATE INDEX ORDER_ID ON TRANSACT(ORDER_ID);
     CREATE INDEX ORDERID ON STOCK(ORDER_ID);
     
     SELECT TRANSACT.ORDER_ID FROM TRANSACT, STOCK
         WHERE TRANSACT.ORDER_ID = STOCK.ORDER_ID 
         AND TRANSACT.ORDER_ID = '87-105';

WORK-AROUND:

     Drop one of the indexes.

     Note:  The two-table join (WHERE TRANSACT.ORDER_ID =
     STOCK.ORDER_ID), the indexes on both joined columns, and the
     predicate condition local to the TRANSACT table.  Dropping either
     index will allow the query to succeed.