The short answer (as you've seen firsthand):  No, you cannot do that.

You can do subqueries like this:

select * from MySource where id in (select id from SomeOtherSource where condition=whatever)

On 4/15/2022 9:49 AM, Jeff Roberts wrote:

I've been watching a video tutorial called "Mastery with SQL" that uses Postgres for the back end. At one point while talking about subqueries the instructor runs the following SQL query:

SELECT * FROM (values ('short', 0, 60), ('medium', 60, 120), ('long', 120, 10000)) as c("desc", "min", "max");

This query more or less creates a table and then selects everything out of it. Not very useful on its own but could be handy when joined up to a real table. I get a syntax error running this in Visual Foxpro 9 so I was wondering if the syntax can be changed so that it will work, or is this type of query even possible with Visual Foxpro? I think it could be done with a create cursor statement ahead of the query but I'm just curious if it can be done with a one-liner. FYI the query as-is works in Postgres but not VFP, Sqlite, or Mysql (at least the versions I have on my machine).

It's a pain to accomplish, and would probably be faster just to create the cursor in code

CREATE CURSOR tempCursor (selected l) INSERT INTO tempCursor VALUES (.T.)

SELECT "short" as desc, 0 as min, 60 as max FROM tempCursor ; union SELECT "medium" as desc, 60 as min, 120 as max FROM tempCursor ; union SELECT "long" as desc, 120 as min, 1000 as max FROM tempCursor INTO CURSOR testing

SELECT testing BROWSE

In this scenario the tempCursor needs to exist and have only 1 record in it for the speed of the Unions to be fast. Each of those select statements will create a result with the number of records in tempCursor. Sure, you could use and Order By clause and Top 1 to get just one result, but the Order By could slow it down.

I'm with Mike, just don't do it.

Tracy

-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Jeff Roberts Sent: Friday, April 15, 2022 9:49 AM To: profoxtech@leafe.com Subject: can visual foxpro create a table inside a sql statement?

I've been watching a video tutorial called "Mastery with SQL" that uses Postgres for the back end. At one point while talking about subqueries the instructor runs the following SQL query:

SELECT * FROM (values ('short', 0, 60), ('medium', 60, 120), ('long', 120, 10000)) as c("desc", "min", "max");

This query more or less creates a table and then selects everything out of it. Not very useful on its own but could be handy when joined up to a real table. I get a syntax error running this in Visual Foxpro 9 so I was wondering if the syntax can be changed so that it will work, or is this type of query even possible with Visual Foxpro? I think it could be done with a create cursor statement ahead of the query but I'm just curious if it can be done with a one-liner. FYI the query as-is works in Postgres but not VFP, Sqlite, or Mysql (at least the versions I have on my machine).