Last week, our FoxPro discussion group discussed a problem raised because the View Designer would not save a view. The example was very simple -- a MAIN table with two lookup tables linked to it:

1) Create a new database, I call it SQL_test.

2) Add a table MAIN with three fields: product, state_code, and prod_code.

3) Create a STATES lookup table with fields state_code and state_name.

4) Create a lookup table for product types called PRODUCTS with fields prod_code and prod_name. (All code fields are C,2)

Add a few records to each table -- enough to see what is happening. I had three kinds of products and the states NJ, NY and MA. Do NOT create any indexes for now.

5) Now create a new local view. Add the tables MAIN, PRODUCTS, and STATES. The view designer suggests joins when the PRODUCTS and STATES tables are added. We used left outer joins, but the same thing happens with any of the four types. Add all the fields in the three tables to the view.

6) Run the query: The results are incorrect. In my test data, I get "NJ" in STATES.STATE_CODE even though each record in MAIN has a dfferent STATE_CODE.

7) Save the view: You get an error message that the column STATE_CODE is not found and then a dialog box that the view cannot be saved.

We experimented at the meeting. If you copy the code from the Show SQL box and run it in the command window, you get no error message, but the results are wrong (all those "NJ"s!).

Bad code:
SELECT *;
FROM sql_test!main INNER JOIN sql_test!products;
INNER JOIN sql_test!states ;
ON Main.state_code = States.state_code ;
ON Main.prod_code = Products.prod_code

If you change the format of the SELECT from the ANSI '92 nested structure to a sequential one, the results cursor is correct:

Good code:
SELECT *;
FROM sql_test!main INNER JOIN sql_test!products;
ON Main.prod_code = Products.prod_code;
INNER JOIN sql_test!states ;
ON Main.state_code = States.state_code

We also believe that this problem only occurs when the view is of the type A->B, A->C (i.e., two relations from one parent); not when the view is of the type A->B->C.

Last night I played with this further: if you index the prod_code and state_code fields in all three tables, create presistent relationships between MAIN and each of the lookups, and then create your view, there is no problem. The View Designer now saves the same code it wouldn't save before, and the results are correct regardless of whether you use ASNI '92 format or not. (Strangely, after the view is saved, you can remove the indexes, and it still works.)

Am I missing something here? I cannot find anything in the docs that suggests that relationships in the underlying database are required to set a join in a view. If VFP expects a persistent relationship before the view is built, it would be nice if we got an appropriate error message. If this is a bug, it occurs in VFP 5.0 and 5.0a. Any thoughts? Thanx.

Steve