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-boun...@leafe.com] On Behalf Of Jeff
Roberts
Sent: Friday, April 15, 2022 9:49 AM
To: profoxt...@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).
--
Jeff Roberts | jefflrobe...@gmail.com
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message:
https://leafe.com/archives/byMID/001801d850f6$2dd65df0$898319d0$@powerchurch.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
