General information
Category:
Coding, syntax & commands
>Howdy,
>
>I'm simply trying to create a View through the View designer which connects my main table to 2 other tables by way of outer joins.
>
>When I do it in the view designer, and save it - it will run correctly, but when I try to modidy the view - ir crashes.
>It seems that the view designer creates the SQL syntax incorrectly.
>
>1. This is what the View Designer gives me as the SQL syntax
>
>SELECT MAIN.ID, NAMES.FNAME, STATES.STATE ;
> FROM MAIN LEFT OUTER JOIN NAMES;
> LEFT OUTER JOIN STATES ;
> ON MAIN.name = STATES.name ;
> ON MAIN.id = NAMES.id
>
>2. Running it is O.K.
>
>3. When I save the view and try to modify it I get the Fox error:
>SQL: Column 'NAME' is not found.
>
>John F.
>Needs View help in NYC
John,
This parent and two children (or master table with two lookups) was discussed at Tamar Grainor's session at DevCon -- it is definitely a limitation (I read that as bug) in the view designer.
When writing SQL code for this in VFP, I have better results using sequential listing of joins, e.g.,
SELECT MAIN.ID, NAMES.FNAME, STATES.STATE ;
FROM MAIN LEFT OUTER JOIN NAMES;
ON MAIN.id = NAMES.id;
LEFT OUTER JOIN STATES ;
ON MAIN.name = STATES.name
instead of the nested joins in your example (which, of course, is what the view designer creates).
HTH.
Steve
(I know a number of apts in NYC that need view help.)
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only