embedded select statements

[thewaterguy]

Could someone direct me to site where I can find the proper syntax for creating advance queries with VFP 8.0

I'm trying a simple embedded SELECT statement and can't seem to get it to work.

i.e.
SELECT newfield*3 AS f_Flow FROM (Select flow as newfield FROM plant) INTO CURSOR csrTest

 

[Mike Lewis]

Could someone direct me to site where I can find the proper syntax for creating advance queries with VFP 8.0

You've found it.

SELECT newfield*3 AS f_Flow FROM (Select flow as newfield FROM plant) INTO CURSOR csrTest

This particular syntax isn't supported below VFP 9.0.

In fact, off-hand I can't see what you are trying to achieve that you can't do with a simple query:

SELECT flow * 3 AS f_Flow FROM plant INTO CURSOR csrTest

Or am I missing something?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[thewaterguy]

Thanks for the response Mike. I was more interested in finding examples that show the proper syntax for writing queries using embedded select statements in VFP 8. The example I threw out was more of what I meant by an embedded select statement and a test to see if I could get any results out in VFP 8. I didn't realize that I need version 9. I did a search on this site with serveral different keywords on this site to see if I could get some examples and I didn't get any results back.

 

[StewartUK]

Tamar Granor has written a book all about SQL statements in VFP9 and you can find out about it here:

http://www.hentzenwerke.com/catalog/tamingvfpsql.htm.

Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Lewis]

Thewaterguy,

In VFP 8.0 and below, you can use a subquery (what you can an "embedded Select") in a WHERE clause. Some examples:

* Find invoices that haven't been paid
SELECT Cust_ID, Amount FROM Invoices ;
  WHERe Cust_ID NOT IN (SELECT Cust_ID FROM Payments)

* Find above-average orders
SELECT * FROM Orders ;
  WHERE Amount > (SELECT AVG(Amount) FROM Orders)

However, there's about all you can. And you can only nest a subquery down to one level.

In VFP 9.0, you can put the subquery in the FROM clause (a so-called derived table) or the JOIN clause (a projection). You can also nest subqueries quite deeply. Plus there's lots more you can do with them.

You know, all of this is in the Help. If you are interested in learning the capabilities of SELECT, the relevant Help topic would be a good place to start.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk