Need more view help! 
Author Message
 Need more view help!

OK, I'm stuck again. I'm using views in preparation for a client/server
conversion. I have to run a query that has one parent table and several
child tables. Recently, I posted help here getting the sql to work because
of what appears to be a bug in the query designer, IE if you have multiple
child tables at the same level from the parent, it doesn't put the JOIN and
ON clauses in the proper order.

Herein lies the problem - I can create the sql by hand, and it works very
well. But how do you run a hand created sql statement against a server
database?

Example: I have a backend database with a couple million records. I want to
run a query that might look like this:

SELECT * FROM TABLE1, TABLE2.FIELD2, TABLE3.FIELD3 FROM:
    TABLE1 INNER JOIN TABLE1.PK ON TABLE1.PK = TABLE2.FK;
    TABLE2 INNER JOIN TABLE1.PK ON PK = TABLE3.FK;

OK, this should be a simple enough example. One parent table, 2 child
tables. The query/view designer will create this with the INNER JOIN and ON
clauses in the wrong order, so it won't work. Because of this, I can't use
the view designer to create the view! How can I create a view, or otherwise
use the above syntax against a back end database without creating a view in
the view designer?

Better yet, how can I create SQL on the fly and execute that against a back
end database? I want to create a parameterized view so all I have to do is
set the proper variables and requery the view and be done with it. The
actual sql depends on what values the user selects, there are actually 5
fields that can be used to filter the results.



Thu, 29 Jul 2004 08:46:56 GMT  
 Need more view help!
FWIW - I am currently able to do this by using the VFP3 view designer to
create the view. This seems totally wrong, that I have to use the view
designer from 3 versions back just to get the views to work. What am I
missing? I'm incredulous that Microsoft would break the view/query designer
in VFP5 and leave it broken in VFP6 and VFP7. What am I missing?


Quote:
> OK, I'm stuck again. I'm using views in preparation for a client/server
> conversion. I have to run a query that has one parent table and several
> child tables. Recently, I posted help here getting the sql to work because
> of what appears to be a bug in the query designer, IE if you have multiple
> child tables at the same level from the parent, it doesn't put the JOIN
and
> ON clauses in the proper order.

> Herein lies the problem - I can create the sql by hand, and it works very
> well. But how do you run a hand created sql statement against a server
> database?

> Example: I have a backend database with a couple million records. I want
to
> run a query that might look like this:

> SELECT * FROM TABLE1, TABLE2.FIELD2, TABLE3.FIELD3 FROM:
>     TABLE1 INNER JOIN TABLE1.PK ON TABLE1.PK = TABLE2.FK;
>     TABLE2 INNER JOIN TABLE1.PK ON PK = TABLE3.FK;

> OK, this should be a simple enough example. One parent table, 2 child
> tables. The query/view designer will create this with the INNER JOIN and
ON
> clauses in the wrong order, so it won't work. Because of this, I can't use
> the view designer to create the view! How can I create a view, or
otherwise
> use the above syntax against a back end database without creating a view
in
> the view designer?

> Better yet, how can I create SQL on the fly and execute that against a
back
> end database? I want to create a parameterized view so all I have to do is
> set the proper variables and requery the view and be done with it. The
> actual sql depends on what values the user selects, there are actually 5
> fields that can be used to filter the results.



Thu, 29 Jul 2004 08:54:53 GMT  
 Need more view help!

Quote:
> Herein lies the problem - I can create the sql by hand, and it works very
> well.

IMO it's better to create the views programmatically. The advantage is
that one can have the entire view definition in a separate ascii.prg and be
independent of any (buggy) view designer.

Create View vTest As ;
SELECT * FROM TABLE1, TABLE2.FIELD2, TABLE3.FIELD3 FROM:
TABLE1 INNER JOIN TABLE1.PK ON TABLE1.PK = TABLE2.FK;
TABLE2 INNER JOIN TABLE1.PK ON PK = TABLE3.FK

Quote:
> I want to create a parameterized view so all I have to do is
> set the proper variables and requery the view and be done with it.

    Create View ....
    Where Table1.somefield = ?YourParameter ...

To be updatable the view needs to have a few additional Dbsetprop()
statements. You could for example create a simple test (remote) view
with the Vfp View Designer and then run
Home()+"tools\gendbc\gendbc.prg" to get the entire set.

Quote:
> Better yet, how can I create SQL on the fly and execute that against a back
> end database?

Have a look at Sqlexec()
    lcSQL = "SELECT ...
    SQLEXEC(lnHnd,lcSQL)
    BROWSE
The resulting cursor can be updatable too by using Cursorsetprop()
similar to the Dbsetprops commands above.

HTH
-Stefan


Quote:
> OK, I'm stuck again. I'm using views in preparation for a client/server
> conversion. I have to run a query that has one parent table and several
> child tables. Recently, I posted help here getting the sql to work because
> of what appears to be a bug in the query designer, IE if you have multiple
> child tables at the same level from the parent, it doesn't put the JOIN and
> ON clauses in the proper order.

> Herein lies the problem - I can create the sql by hand, and it works very
> well. But how do you run a hand created sql statement against a server
> database?

> Example: I have a backend database with a couple million records. I want to
> run a query that might look like this:

> SELECT * FROM TABLE1, TABLE2.FIELD2, TABLE3.FIELD3 FROM:
>     TABLE1 INNER JOIN TABLE1.PK ON TABLE1.PK = TABLE2.FK;
>     TABLE2 INNER JOIN TABLE1.PK ON PK = TABLE3.FK;

> OK, this should be a simple enough example. One parent table, 2 child
> tables. The query/view designer will create this with the INNER JOIN and ON
> clauses in the wrong order, so it won't work. Because of this, I can't use
> the view designer to create the view! How can I create a view, or otherwise
> use the above syntax against a back end database without creating a view in
> the view designer?

> Better yet, how can I create SQL on the fly and execute that against a back
> end database? I want to create a parameterized view so all I have to do is
> set the proper variables and requery the view and be done with it. The
> actual sql depends on what values the user selects, there are actually 5
> fields that can be used to filter the results.



Thu, 29 Jul 2004 20:22:45 GMT  
 Need more view help!
You may want to try Steve Sawyer's ViewEdit available at
http://www.geeksandgurus.com/vfptools/vieweditor.htm.

Rick


Quote:
> OK, I'm stuck again. I'm using views in preparation for a client/server
> conversion. I have to run a query that has one parent table and several
> child tables. Recently, I posted help here getting the sql to work because
> of what appears to be a bug in the query designer, IE if you have multiple
> child tables at the same level from the parent, it doesn't put the JOIN
and
> ON clauses in the proper order.

> Herein lies the problem - I can create the sql by hand, and it works very
> well. But how do you run a hand created sql statement against a server
> database?

> Example: I have a backend database with a couple million records. I want
to
> run a query that might look like this:

> SELECT * FROM TABLE1, TABLE2.FIELD2, TABLE3.FIELD3 FROM:
>     TABLE1 INNER JOIN TABLE1.PK ON TABLE1.PK = TABLE2.FK;
>     TABLE2 INNER JOIN TABLE1.PK ON PK = TABLE3.FK;

> OK, this should be a simple enough example. One parent table, 2 child
> tables. The query/view designer will create this with the INNER JOIN and
ON
> clauses in the wrong order, so it won't work. Because of this, I can't use
> the view designer to create the view! How can I create a view, or
otherwise
> use the above syntax against a back end database without creating a view
in
> the view designer?

> Better yet, how can I create SQL on the fly and execute that against a
back
> end database? I want to create a parameterized view so all I have to do is
> set the proper variables and requery the view and be done with it. The
> actual sql depends on what values the user selects, there are actually 5
> fields that can be used to filter the results.



Sat, 31 Jul 2004 03:44:26 GMT  
 Need more view help!
Would you please send me a (eval) copy of the follow ?

I can't reach that site, prohaps my government block us.

TIA

max.

Quote:
>You may want to try Steve Sawyer's ViewEdit available at
>http://www.geeksandgurus.com/vfptools/vieweditor.htm.



Sun, 01 Aug 2004 11:46:25 GMT  
 Need more view help!
I would, but there are two different versions - one for VFP 6.0, and one for
VFP 7.0. Since the files are ~315kb zipped up, both would probably over fill
most mail boxes. Note: These are freeware - no charge - Steve is current
sharing this with the FP community, and just asking for feedback.

Rick


Quote:
> Would you please send me a (eval) copy of the follow ?

> I can't reach that site, prohaps my government block us.

> TIA

> max.

> >You may want to try Steve Sawyer's ViewEdit available at
> >http://www.geeksandgurus.com/vfptools/vieweditor.htm.



Sun, 01 Aug 2004 22:37:50 GMT  
 Need more view help!
Hi Rick,


capable for 4 megabyte.

TIA

max.

Wed, 13 Feb 2002 09:37:50 -0500 , "Rick Bean"

Quote:
>I would, but there are two different versions - one for VFP 6.0, and one for
>VFP 7.0. Since the files are ~315kb zipped up, both would probably over fill
>most mail boxes. Note: These are freeware - no charge - Steve is current
>sharing this with the FP community, and just asking for feedback.

>Rick



>> Would you please send me a (eval) copy of the follow ?

>> I can't reach that site, prohaps my government block us.

>> TIA

>> max.

>> >You may want to try Steve Sawyer's ViewEdit available at
>> >http://www.geeksandgurus.com/vfptools/vieweditor.htm.



Fri, 13 Aug 2004 10:40:33 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Need help with Views

2. Need help with views and grids

3. View help needed

4. I NEED HELP I NEED HELP CUSTOMIZING EXCHANGE 5.0

5. HELP,HELP,HELP need help fast

6. I Need Help needed generating a special report!!!

7. Help needed with ONLINE HELP system inside .EXE file

8. Need Help For Customize Help In Foxpro for Dos 2.5

9. (*.HLP) : Need help to build Windows 3.x Help Files

10. Need more help with HELP!!!!!!

11. Help Newibe needs Basic help

12. HELP! NOVICE NEEDS VFP 5 REPORT HELP

 

 
Powered by phpBB® Forum Software