VFP8 - View designer fixed!!! 
Author Message
 VFP8 - View designer fixed!!!

Yes! Yes! They fixed it!!! Finally, the view designer will properly place
the LEFT OUTER JOIN...ON...statements!!!

VFP6-7 (and maybe 5, I forget)

SELECT Look.ook, Look1.ook1, Look2.ook2, Look3.ook3;
 FROM  ook!look LEFT OUTER JOIN ook!look1;
    LEFT OUTER JOIN ook!look2;
    LEFT OUTER JOIN ook!look3 ;
   ON  Look.ook = Look3.ook ;
   ON  Look.ook = Look2.ook ;
   ON  Look.ook = Look1.ook

Notice that this DOES NOT WORK! The columns for ook2 and ook3 will not have
the correct valuves in them. This has been a BUG in the view designer for
the last several versions of VFP, and has caused me no end of grief as I'd
have to manually edit the sql and use viewedit to update the dbc. Also, not
that the above causes a "Query Parse Error" when you try to save the query -
easily reproducable, I've seen it many many times.

VFP8

SELECT Look.ook, Look1.ook1, Look2.ook2, Look3.ook3;
 FROM ;
     ook!look ;
    LEFT OUTER JOIN ook!look1 ;
   ON  Look.ook = Look1.ook ;
    LEFT OUTER JOIN ook!look3 ;
   ON  Look.ook = Look3.ook ;
    LEFT OUTER JOIN ook!look2 ;
   ON  Look.ook = Look2.ook

This works as expected. The view designer will now properly place the LEFT
OUTER JOIN and the ONs.

Microsoft deserves kudos for finally fixing it, but they also deserve a big
DOH! for taking so long to do something about it.



Mon, 25 Apr 2005 10:22:48 GMT  
 VFP8 - View designer fixed!!!
It looks like I spoke too soon. I've been able to duplicate where the view
designer will create remote views with bad sql under certain circumstances.

If, while adding additional tables, I at that time I add the additional
tables select the type of join (I'm using left outer join), then after I
save the view I can't get back into it.

If, when I add the tables, I press ESC at the join dialog and don't select a
join type at that time, and then after adding the tables come back and add
the joins, it works fine.

This is the good sql:

SELECT OOK.OOK, ;
  OOK1.OOK1, ;
  OOK2.OOK2, ;
  OOK3.OOK3 ;
 FROM DBO.OOK OOK INNER ;
  JOIN DBO.OOK1 OOK1 ;
    ON OOK.OOK = OOK1.OOK INNER ;
  JOIN DBO.OOK3 OOK3 ;
    ON OOK.OOK = OOK3.OOK INNER ;
  JOIN DBO.OOK2 OOK2 ;
    ON OOK.OOK = OOK2.OOK

This is what it does to the bad sql:

SELECT OOK.OOK, ;
  OOK1.OOK1, ;
  OOK2.OOK2, ;
  OOK3.OOK3 ;
 FROM {OJ{OJ{OJ DBO.OOK OOK ;
  LEFT JOIN DBO.OOK1 OOK1 ;
    ON OOK.OOK = OOK1.OOK} ;
  LEFT JOIN DBO.OOK3 OOK3 ;
    ON OOK.OOK = OOK3.OOK} ;
  LEFT JOIN DBO.OOK2 OOK2 ;
    ON OOK.OOK = OOK2.OOK}

Look at the FROM line to see what it is doing. If I manually edit the view
and take out the '{OJ' and the '}', it then works fine. I can post detailed
procedure to duplicate this if anyone is interested.



Mon, 25 Apr 2005 10:58:43 GMT  
 VFP8 - View designer fixed!!!
What ODBC driver are using? Looks to me that VFP is trying to create ODBC
outer join escape sequences.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc...
bcouter_join_escape_sequence.asp

They should be OK, but the ODB driver you're using might not corectly
implement them.

Remus



Quote:
> It looks like I spoke too soon. I've been able to duplicate where the view
> designer will create remote views with bad sql under certain
circumstances.

> If, while adding additional tables, I at that time I add the additional
> tables select the type of join (I'm using left outer join), then after I
> save the view I can't get back into it.

> If, when I add the tables, I press ESC at the join dialog and don't select
a
> join type at that time, and then after adding the tables come back and add
> the joins, it works fine.

> This is the good sql:

> SELECT OOK.OOK, ;
>   OOK1.OOK1, ;
>   OOK2.OOK2, ;
>   OOK3.OOK3 ;
>  FROM DBO.OOK OOK INNER ;
>   JOIN DBO.OOK1 OOK1 ;
>     ON OOK.OOK = OOK1.OOK INNER ;
>   JOIN DBO.OOK3 OOK3 ;
>     ON OOK.OOK = OOK3.OOK INNER ;
>   JOIN DBO.OOK2 OOK2 ;
>     ON OOK.OOK = OOK2.OOK

> This is what it does to the bad sql:

> SELECT OOK.OOK, ;
>   OOK1.OOK1, ;
>   OOK2.OOK2, ;
>   OOK3.OOK3 ;
>  FROM {OJ{OJ{OJ DBO.OOK OOK ;
>   LEFT JOIN DBO.OOK1 OOK1 ;
>     ON OOK.OOK = OOK1.OOK} ;
>   LEFT JOIN DBO.OOK3 OOK3 ;
>     ON OOK.OOK = OOK3.OOK} ;
>   LEFT JOIN DBO.OOK2 OOK2 ;
>     ON OOK.OOK = OOK2.OOK}

> Look at the FROM line to see what it is doing. If I manually edit the view
> and take out the '{OJ' and the '}', it then works fine. I can post
detailed
> procedure to duplicate this if anyone is interested.



Mon, 25 Apr 2005 11:55:29 GMT  
 VFP8 - View designer fixed!!!
eek - smells like you're gonna have to hack the view designer to take out
the f'ing curly braces.
good catch on the defect, though - OOK ROCKS!!!!
mondo regards [Bill]

--
=============================[remove the dot bob to reply]
William Sanders, Electronic Filing Group. MSDN ISV - VFP/SQL .  mySql/ Sql /
Oracle with VFP - YUP!!
972.394.7261 / GoTo China and teach C/SaPPdEV on occasion.
http://window.to/vfoxpro


Quote:
> It looks like I spoke too soon. I've been able to duplicate where the view
> designer will create remote views with bad sql under certain
circumstances.

> If, while adding additional tables, I at that time I add the additional
> tables select the type of join (I'm using left outer join), then after I
> save the view I can't get back into it.

> If, when I add the tables, I press ESC at the join dialog and don't select
a
> join type at that time, and then after adding the tables come back and add
> the joins, it works fine.

> This is the good sql:

> SELECT OOK.OOK, ;
>   OOK1.OOK1, ;
>   OOK2.OOK2, ;
>   OOK3.OOK3 ;
>  FROM DBO.OOK OOK INNER ;
>   JOIN DBO.OOK1 OOK1 ;
>     ON OOK.OOK = OOK1.OOK INNER ;
>   JOIN DBO.OOK3 OOK3 ;
>     ON OOK.OOK = OOK3.OOK INNER ;
>   JOIN DBO.OOK2 OOK2 ;
>     ON OOK.OOK = OOK2.OOK

> This is what it does to the bad sql:

> SELECT OOK.OOK, ;
>   OOK1.OOK1, ;
>   OOK2.OOK2, ;
>   OOK3.OOK3 ;
>  FROM {OJ{OJ{OJ DBO.OOK OOK ;
>   LEFT JOIN DBO.OOK1 OOK1 ;
>     ON OOK.OOK = OOK1.OOK} ;
>   LEFT JOIN DBO.OOK3 OOK3 ;
>     ON OOK.OOK = OOK3.OOK} ;
>   LEFT JOIN DBO.OOK2 OOK2 ;
>     ON OOK.OOK = OOK2.OOK}

> Look at the FROM line to see what it is doing. If I manually edit the view
> and take out the '{OJ' and the '}', it then works fine. I can post
detailed
> procedure to duplicate this if anyone is interested.



Mon, 25 Apr 2005 12:25:25 GMT  
 VFP8 - View designer fixed!!!
I have done this using the mysql odbc driver, and the Microsoft SQL Server
odbc driver - they both do it. And I think it's definitely related to the
left outer join syntax. When I try to edit the view, it tells me "Command
contains unrecognized phrase/keyword", as if the view designer itself
doesn't like the syntax it just created. Interesting enough, the same sql
gives the same error in VFP7. I think there is a problem with the left outer
join syntax the view designer creates, neither VFP6 nor VFP7 likes it.

What determines the join syntax? Is it from the odbc driver? I can
understand the mysql driver being flaky, but I'm also using the MS SQL
Server odbc driver, I would expect that to work properly?


Quote:
> What ODBC driver are using? Looks to me that VFP is trying to create ODBC
> outer join escape sequences.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc...
Quote:
> bcouter_join_escape_sequence.asp

> They should be OK, but the ODB driver you're using might not corectly
> implement them.

> Remus



> > It looks like I spoke too soon. I've been able to duplicate where the
view
> > designer will create remote views with bad sql under certain
> circumstances.

> > If, while adding additional tables, I at that time I add the additional
> > tables select the type of join (I'm using left outer join), then after I
> > save the view I can't get back into it.

> > If, when I add the tables, I press ESC at the join dialog and don't
select
> a
> > join type at that time, and then after adding the tables come back and
add
> > the joins, it works fine.

> > This is the good sql:

> > SELECT OOK.OOK, ;
> >   OOK1.OOK1, ;
> >   OOK2.OOK2, ;
> >   OOK3.OOK3 ;
> >  FROM DBO.OOK OOK INNER ;
> >   JOIN DBO.OOK1 OOK1 ;
> >     ON OOK.OOK = OOK1.OOK INNER ;
> >   JOIN DBO.OOK3 OOK3 ;
> >     ON OOK.OOK = OOK3.OOK INNER ;
> >   JOIN DBO.OOK2 OOK2 ;
> >     ON OOK.OOK = OOK2.OOK

> > This is what it does to the bad sql:

> > SELECT OOK.OOK, ;
> >   OOK1.OOK1, ;
> >   OOK2.OOK2, ;
> >   OOK3.OOK3 ;
> >  FROM {OJ{OJ{OJ DBO.OOK OOK ;
> >   LEFT JOIN DBO.OOK1 OOK1 ;
> >     ON OOK.OOK = OOK1.OOK} ;
> >   LEFT JOIN DBO.OOK3 OOK3 ;
> >     ON OOK.OOK = OOK3.OOK} ;
> >   LEFT JOIN DBO.OOK2 OOK2 ;
> >     ON OOK.OOK = OOK2.OOK}

> > Look at the FROM line to see what it is doing. If I manually edit the
view
> > and take out the '{OJ' and the '}', it then works fine. I can post
> detailed
> > procedure to duplicate this if anyone is interested.



Mon, 25 Apr 2005 13:52:50 GMT  
 VFP8 - View designer fixed!!!
I cannot test your select, but I try one from scratch and it sure works OK
with SQL2000:

? SQLEXEC(1,"create table t1 (t int primary key)")
? SQLEXEC(1,"create table t2 (t int primary key)")
? SQLEXEC(1,"create table t3 (t int primary key)")
? SQLEXEC(1,"insert into t1 values (1)")
? SQLEXEC(1,"insert into t1 values (2)")
? SQLEXEC(1,"insert into t1 values (3)")
? SQLEXEC(1,"insert into t2 values (1)")
? SQLEXEC(1,"insert into t3 values (2)")

? SQLEXEC(1,"select * from {oj {oj t1 left outer join t2 on t1.t = t2.t}
left outer join t3 on t1.t = t3.t}")
BROWSE
? SQLEXEC(1,"select * from {oj {oj t1 left join t2 on t1.t = t2.t} left join
t3 on t1.t = t3.t}")
BROWSE

Remus



Quote:
> I have done this using the mysql odbc driver, and the Microsoft SQL Server
> odbc driver - they both do it. And I think it's definitely related to the
> left outer join syntax. When I try to edit the view, it tells me "Command
> contains unrecognized phrase/keyword", as if the view designer itself
> doesn't like the syntax it just created. Interesting enough, the same sql
> gives the same error in VFP7. I think there is a problem with the left
outer
> join syntax the view designer creates, neither VFP6 nor VFP7 likes it.

> What determines the join syntax? Is it from the odbc driver? I can
> understand the mysql driver being flaky, but I'm also using the MS SQL
> Server odbc driver, I would expect that to work properly?



> > What ODBC driver are using? Looks to me that VFP is trying to create
ODBC
> > outer join escape sequences.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc...

- Show quoted text -

Quote:
> > bcouter_join_escape_sequence.asp

> > They should be OK, but the ODB driver you're using might not corectly
> > implement them.

> > Remus



> > > It looks like I spoke too soon. I've been able to duplicate where the
> view
> > > designer will create remote views with bad sql under certain
> > circumstances.

> > > If, while adding additional tables, I at that time I add the
additional
> > > tables select the type of join (I'm using left outer join), then after
I
> > > save the view I can't get back into it.

> > > If, when I add the tables, I press ESC at the join dialog and don't
> select
> > a
> > > join type at that time, and then after adding the tables come back and
> add
> > > the joins, it works fine.

> > > This is the good sql:

> > > SELECT OOK.OOK, ;
> > >   OOK1.OOK1, ;
> > >   OOK2.OOK2, ;
> > >   OOK3.OOK3 ;
> > >  FROM DBO.OOK OOK INNER ;
> > >   JOIN DBO.OOK1 OOK1 ;
> > >     ON OOK.OOK = OOK1.OOK INNER ;
> > >   JOIN DBO.OOK3 OOK3 ;
> > >     ON OOK.OOK = OOK3.OOK INNER ;
> > >   JOIN DBO.OOK2 OOK2 ;
> > >     ON OOK.OOK = OOK2.OOK

> > > This is what it does to the bad sql:

> > > SELECT OOK.OOK, ;
> > >   OOK1.OOK1, ;
> > >   OOK2.OOK2, ;
> > >   OOK3.OOK3 ;
> > >  FROM {OJ{OJ{OJ DBO.OOK OOK ;
> > >   LEFT JOIN DBO.OOK1 OOK1 ;
> > >     ON OOK.OOK = OOK1.OOK} ;
> > >   LEFT JOIN DBO.OOK3 OOK3 ;
> > >     ON OOK.OOK = OOK3.OOK} ;
> > >   LEFT JOIN DBO.OOK2 OOK2 ;
> > >     ON OOK.OOK = OOK2.OOK}

> > > Look at the FROM line to see what it is doing. If I manually edit the
> view
> > > and take out the '{OJ' and the '}', it then works fine. I can post
> > detailed
> > > procedure to duplicate this if anyone is interested.



Tue, 26 Apr 2005 02:58:04 GMT  
 VFP8 - View designer fixed!!!
Remus,

This issue has nothing to do with whether or not the sql works - your sql
works, and so does mine. The issue here is that the view designer is broken
in that once you create and save such a view useing left outer joins, you
will no longer be able to edit it in the view designer. The sql works, you
can USE the view, but you just can't get the view designer to open it up
again without giving an error. I took your sql and used viewedit to insert
it into an existing view, and it does the exact same thing as my sql does.
The sql is good, the view can be USED, but the view designer is broken.


Quote:
> I cannot test your select, but I try one from scratch and it sure works OK
> with SQL2000:

> ? SQLEXEC(1,"create table t1 (t int primary key)")
> ? SQLEXEC(1,"create table t2 (t int primary key)")
> ? SQLEXEC(1,"create table t3 (t int primary key)")
> ? SQLEXEC(1,"insert into t1 values (1)")
> ? SQLEXEC(1,"insert into t1 values (2)")
> ? SQLEXEC(1,"insert into t1 values (3)")
> ? SQLEXEC(1,"insert into t2 values (1)")
> ? SQLEXEC(1,"insert into t3 values (2)")

> ? SQLEXEC(1,"select * from {oj {oj t1 left outer join t2 on t1.t = t2.t}
> left outer join t3 on t1.t = t3.t}")
> BROWSE
> ? SQLEXEC(1,"select * from {oj {oj t1 left join t2 on t1.t = t2.t} left
join
> t3 on t1.t = t3.t}")
> BROWSE

> Remus



> > I have done this using the mysql odbc driver, and the Microsoft SQL
Server
> > odbc driver - they both do it. And I think it's definitely related to
the
> > left outer join syntax. When I try to edit the view, it tells me
"Command
> > contains unrecognized phrase/keyword", as if the view designer itself
> > doesn't like the syntax it just created. Interesting enough, the same
sql
> > gives the same error in VFP7. I think there is a problem with the left
> outer
> > join syntax the view designer creates, neither VFP6 nor VFP7 likes it.

> > What determines the join syntax? Is it from the odbc driver? I can
> > understand the mysql driver being flaky, but I'm also using the MS SQL
> > Server odbc driver, I would expect that to work properly?



> > > What ODBC driver are using? Looks to me that VFP is trying to create
> ODBC
> > > outer join escape sequences.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc...

- Show quoted text -

Quote:
> > > bcouter_join_escape_sequence.asp

> > > They should be OK, but the ODB driver you're using might not corectly
> > > implement them.

> > > Remus



> > > > It looks like I spoke too soon. I've been able to duplicate where
the
> > view
> > > > designer will create remote views with bad sql under certain
> > > circumstances.

> > > > If, while adding additional tables, I at that time I add the
> additional
> > > > tables select the type of join (I'm using left outer join), then
after
> I
> > > > save the view I can't get back into it.

> > > > If, when I add the tables, I press ESC at the join dialog and don't
> > select
> > > a
> > > > join type at that time, and then after adding the tables come back
and
> > add
> > > > the joins, it works fine.

> > > > This is the good sql:

> > > > SELECT OOK.OOK, ;
> > > >   OOK1.OOK1, ;
> > > >   OOK2.OOK2, ;
> > > >   OOK3.OOK3 ;
> > > >  FROM DBO.OOK OOK INNER ;
> > > >   JOIN DBO.OOK1 OOK1 ;
> > > >     ON OOK.OOK = OOK1.OOK INNER ;
> > > >   JOIN DBO.OOK3 OOK3 ;
> > > >     ON OOK.OOK = OOK3.OOK INNER ;
> > > >   JOIN DBO.OOK2 OOK2 ;
> > > >     ON OOK.OOK = OOK2.OOK

> > > > This is what it does to the bad sql:

> > > > SELECT OOK.OOK, ;
> > > >   OOK1.OOK1, ;
> > > >   OOK2.OOK2, ;
> > > >   OOK3.OOK3 ;
> > > >  FROM {OJ{OJ{OJ DBO.OOK OOK ;
> > > >   LEFT JOIN DBO.OOK1 OOK1 ;
> > > >     ON OOK.OOK = OOK1.OOK} ;
> > > >   LEFT JOIN DBO.OOK3 OOK3 ;
> > > >     ON OOK.OOK = OOK3.OOK} ;
> > > >   LEFT JOIN DBO.OOK2 OOK2 ;
> > > >     ON OOK.OOK = OOK2.OOK}

> > > > Look at the FROM line to see what it is doing. If I manually edit
the
> > view
> > > > and take out the '{OJ' and the '}', it then works fine. I can post
> > > detailed
> > > > procedure to duplicate this if anyone is interested.



Tue, 26 Apr 2005 04:01:19 GMT  
 VFP8 - View designer fixed!!!
Sorry, I haven't read carefully enough.

Remus



Quote:
> Remus,

> This issue has nothing to do with whether or not the sql works - your sql
> works, and so does mine. The issue here is that the view designer is
broken
> in that once you create and save such a view useing left outer joins, you
> will no longer be able to edit it in the view designer. The sql works, you
> can USE the view, but you just can't get the view designer to open it up
> again without giving an error. I took your sql and used viewedit to insert
> it into an existing view, and it does the exact same thing as my sql does.
> The sql is good, the view can be USED, but the view designer is broken.



> > I cannot test your select, but I try one from scratch and it sure works
OK
> > with SQL2000:

> > ? SQLEXEC(1,"create table t1 (t int primary key)")
> > ? SQLEXEC(1,"create table t2 (t int primary key)")
> > ? SQLEXEC(1,"create table t3 (t int primary key)")
> > ? SQLEXEC(1,"insert into t1 values (1)")
> > ? SQLEXEC(1,"insert into t1 values (2)")
> > ? SQLEXEC(1,"insert into t1 values (3)")
> > ? SQLEXEC(1,"insert into t2 values (1)")
> > ? SQLEXEC(1,"insert into t3 values (2)")

> > ? SQLEXEC(1,"select * from {oj {oj t1 left outer join t2 on t1.t = t2.t}
> > left outer join t3 on t1.t = t3.t}")
> > BROWSE
> > ? SQLEXEC(1,"select * from {oj {oj t1 left join t2 on t1.t = t2.t} left
> join
> > t3 on t1.t = t3.t}")
> > BROWSE

> > Remus



> > > I have done this using the mysql odbc driver, and the Microsoft SQL
> Server
> > > odbc driver - they both do it. And I think it's definitely related to
> the
> > > left outer join syntax. When I try to edit the view, it tells me
> "Command
> > > contains unrecognized phrase/keyword", as if the view designer itself
> > > doesn't like the syntax it just created. Interesting enough, the same
> sql
> > > gives the same error in VFP7. I think there is a problem with the left
> > outer
> > > join syntax the view designer creates, neither VFP6 nor VFP7 likes it.

> > > What determines the join syntax? Is it from the odbc driver? I can
> > > understand the mysql driver being flaky, but I'm also using the MS SQL
> > > Server odbc driver, I would expect that to work properly?



> > > > What ODBC driver are using? Looks to me that VFP is trying to create
> > ODBC
> > > > outer join escape sequences.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc...

- Show quoted text -

Quote:
> > > > bcouter_join_escape_sequence.asp

> > > > They should be OK, but the ODB driver you're using might not
corectly
> > > > implement them.

> > > > Remus



> > > > > It looks like I spoke too soon. I've been able to duplicate where
> the
> > > view
> > > > > designer will create remote views with bad sql under certain
> > > > circumstances.

> > > > > If, while adding additional tables, I at that time I add the
> > additional
> > > > > tables select the type of join (I'm using left outer join), then
> after
> > I
> > > > > save the view I can't get back into it.

> > > > > If, when I add the tables, I press ESC at the join dialog and
don't
> > > select
> > > > a
> > > > > join type at that time, and then after adding the tables come back
> and
> > > add
> > > > > the joins, it works fine.

> > > > > This is the good sql:

> > > > > SELECT OOK.OOK, ;
> > > > >   OOK1.OOK1, ;
> > > > >   OOK2.OOK2, ;
> > > > >   OOK3.OOK3 ;
> > > > >  FROM DBO.OOK OOK INNER ;
> > > > >   JOIN DBO.OOK1 OOK1 ;
> > > > >     ON OOK.OOK = OOK1.OOK INNER ;
> > > > >   JOIN DBO.OOK3 OOK3 ;
> > > > >     ON OOK.OOK = OOK3.OOK INNER ;
> > > > >   JOIN DBO.OOK2 OOK2 ;
> > > > >     ON OOK.OOK = OOK2.OOK

> > > > > This is what it does to the bad sql:

> > > > > SELECT OOK.OOK, ;
> > > > >   OOK1.OOK1, ;
> > > > >   OOK2.OOK2, ;
> > > > >   OOK3.OOK3 ;
> > > > >  FROM {OJ{OJ{OJ DBO.OOK OOK ;
> > > > >   LEFT JOIN DBO.OOK1 OOK1 ;
> > > > >     ON OOK.OOK = OOK1.OOK} ;
> > > > >   LEFT JOIN DBO.OOK3 OOK3 ;
> > > > >     ON OOK.OOK = OOK3.OOK} ;
> > > > >   LEFT JOIN DBO.OOK2 OOK2 ;
> > > > >     ON OOK.OOK = OOK2.OOK}

> > > > > Look at the FROM line to see what it is doing. If I manually edit
> the
> > > view
> > > > > and take out the '{OJ' and the '}', it then works fine. I can post
> > > > detailed
> > > > > procedure to duplicate this if anyone is interested.



Tue, 26 Apr 2005 04:45:36 GMT  
 VFP8 - View designer fixed!!!
The proper SQL with traling ON-clauses given your ON clauses is

 SELECT Look.ook, Look1.ook1, Look2.ook2, Look3.ook3;
  FROM  ook!look1;
     RIGHT OUTER JOIN ook!look2;
     RIGHT OUTER JOIN ook!look3 ;
    RIGHT JOIN ook!look
    ON  Look.ook = Look3.ook ;
    ON  Look.ook = Look2.ook ;
    ON  Look.ook = Look1.ook

Less intuitive than the SQL in VFP8 as most people would want to place Look
first and link the others from left to right.
Both ways are equally proper and give exactly the same results.

-Anders



Quote:
> Yes! Yes! They fixed it!!! Finally, the view designer will properly place
> the LEFT OUTER JOIN...ON...statements!!!

> VFP6-7 (and maybe 5, I forget)

> SELECT Look.ook, Look1.ook1, Look2.ook2, Look3.ook3;
>  FROM  ook!look LEFT OUTER JOIN ook!look1;
>     LEFT OUTER JOIN ook!look2;
>     LEFT OUTER JOIN ook!look3 ;
>    ON  Look.ook = Look3.ook ;
>    ON  Look.ook = Look2.ook ;
>    ON  Look.ook = Look1.ook

> Notice that this DOES NOT WORK! The columns for ook2 and ook3 will not
have
> the correct valuves in them. This has been a BUG in the view designer for
> the last several versions of VFP, and has caused me no end of grief as I'd
> have to manually edit the sql and use viewedit to update the dbc. Also,
not
> that the above causes a "Query Parse Error" when you try to save the
query -
> easily reproducable, I've seen it many many times.

> VFP8

> SELECT Look.ook, Look1.ook1, Look2.ook2, Look3.ook3;
>  FROM ;
>      ook!look ;
>     LEFT OUTER JOIN ook!look1 ;
>    ON  Look.ook = Look1.ook ;
>     LEFT OUTER JOIN ook!look3 ;
>    ON  Look.ook = Look3.ook ;
>     LEFT OUTER JOIN ook!look2 ;
>    ON  Look.ook = Look2.ook

> This works as expected. The view designer will now properly place the LEFT
> OUTER JOIN and the ONs.

> Microsoft deserves kudos for finally fixing it, but they also deserve a
big
> DOH! for taking so long to do something about it.



Sat, 30 Apr 2005 06:11:17 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. Using the database designer/view designer in .exe

2. vfp8 breakpoints - fixed

3. view designer says error when using mssql functions in remote views

4. Initializing Views created in the View Designer

5. Form designer, VFP8

6. VFP8 - Updateable View slow during TableUpdate

7. VFP8 Remote view gotcha

8. VFP8 remote views connection handle problem

9. VFP8 Task Pane Community view

10. Creating a report from a view with a particular structure (read this with fixed font)

11. Remote views in VFP8

12. VFP8 AutoInc field type not supported in views?

 

 
Powered by phpBB® Forum Software