Thanks for the heads up on the view designer.

But, I'm still confused. Is the SQL from the designer invalid, or does the designer just act up? I pasted the SQL from the view into a PRG, and it acts strangely: the first time I get the error, the next time it runs (if the parent table is already open, then it will work).

I just tried this at home, and I was getting "invalid seek offset" errors. I removed all the indexes, and then I get the "SQL: Column 'SHIP_FOR' is not found
" problem.

All I want is to join a parent table to a child table twice (on 2 different parent fields). One join is inner, the other I'm trying to do an outer. Is there another syntax for doing this?

The designer is generating something like:
SELECT * FROM parent INNER JOIN child LEFT OUTER JOIN child child2 ON parent.f1 = child.f1 ON parent.f2 = child2.f2
Is this kosher SQL, or is it way off base?

Confusedly,
Bob

BTW - it still fails if the LEFT OUTER JOIN is an INNER JOIN

>Hi Robert,
>
>This is a bug in the view designer. There are work-arounds to get over with this one but they only work in some situations. Try creating the view again from scratch and add the detail table first and the parent table at the end.

>
>I would suggest that you maintain the views via a PRG file which contains all the view definitions. Its much easier as far as time is concerned because you do not end up spending a lot of time trying to fix queriers which should be working but don't.
>
>Run the genDbc.prg against your database to get a picture of what settings need to be done for the view.
>
>>I'm trying to use the view designer to create a view, but I get an error with the SQL that it generates (and therefore I can't saveit).
>>
>>The SQL is:
>>
>>SELECT Orders.*;
>> FROM asn!orders INNER JOIN asn!consign;
>> LEFT OUTER JOIN asn!consign Consign_a ;
>> ON Orders.ship_for = Consign_a.consign_id ;
>> ON Orders.consign_id = Consign.consign_id
>>
>>The error is
>>SQL: Column 'SHIP_FOR' is not found
>>
>>BUT, if I run the SQL from the command window, it will fail the first time and succeed the 2nd. I found that if the Orders table is already open, then it will work.
>>
>>What's the deal?
>>
>>Bob