Henrik,
Can you limit the problem by removing the LEFT JOIN and replacing it with a WHERE clause to test if that's the problem? Also, are there any fields that are named the same in the two tables? If so, they will be named MyField_a and MyField_b instead of MyField. This causes problems if they are named in GROUP or ORDER or WHERE/JOIN clauses.

HTH
Barbara

>Hello out there I probably need som help.
>
>I have a very troublesome problem in creating a local view using VFP and the problem applies to both version 5.0a and 6.0. Can anyone please help me out ?
>
>I have a SQL-statement looking like this :
>
>SELECT MainTbl.*, ChildTbl.* FROM SeData!MainTbl LEFT JOIN SeData!ChildTbl ON MainTbl.JustAFld = ChildTbl.KeyFld ORDER BY MainTbl.PrimaryKey
>
>Everything is fine the Query executes and returns the right answers.
>
>But if I try to create a view using either the view-Designer or plain programming :
>
>CREATE SQL VIEW lv_SomeName AS SELECT MainTbl.*, ChildTbl.* FROM SeData!MainTbl LEFT JOIN SeData!ChildTbl ON MainTbl.JustAFld = ChildTbl.KeyFld ORDER BY MainTbl.PrimaryKey
>
>I get various messages all ending up with "Invalid SQL-Statement" !
>
>Sometimes when I create the View in the View-Designer from the Database-Designer I'm able to create the View and running the Query but later when I open the View for editing the View-Designer states that the View is invalid and the different joining-keys (from MainTbl and ChildTbl) doesn't exists.
>
>The Table- and Fieldnames in this example are not the real names, but since I've tried several different tables (and names) this can´t be a point.
>
>Please help
>
>Best Regards Henrik Bønnerup, bonnerup@h-bonnerup.dk