Don't know if this helps but the VFP9 SP2 help file says this:
A sub-SELECT is often referred to as a derived table. Derived tables are SELECT statements in the FROM clause referred to by an alias or a user-specified name. The result set of the SELECT in the FROM clause creates a table used by the outer SELECT statement. Visual FoxPro 9.0 permits the use of a subquery in the FROM clause.
A sub-SELECT should be enclosed in parentheses and an alias is required. Correlation is not supported. A sub-SELECT has the same syntax limitations as the SELECT command, but not the subquery syntax limitations. All sub-SELECTs are executed before the top most SELECT is evaluated.
The following is the general syntax for a subquery in the FROM clause.
SELECT … FROM (SELECT …) [AS] Alias
So it seems that a sub-query MUST have an alias.
Laurie
On 8 August 2017 at 11:54, Ted Roche tedroche@gmail.com wrote:
I don't recall if I've ever run into an error 1801. You go, Gene!
https://msdn.microsoft.com/en-us/library/aa976358(v=vs.71).aspx
Seems to indicate that subqueries don't support the syntax. Note that's an old MSDN entry, so 9.0 could be better.
On Mon, Aug 7, 2017 at 8:35 PM, Gene Wirchenko genew@telus.net wrote:
Hello:
This statement works:select *; from cwkt; where; funccode="TW" and trndate>={^2017.07.01} and; exists; (; select .t. from ctwc; where; cwkt.clcode=ctwc.clcode and cwkt.wccode=ctwc.wccode and; cwkt.trndate>=ctwc.validfr and cwkt.trndate<=ctwc.validto; ); order by clcode,wonbr,trndate
Is there likely to be more than one ctwc record with with a valid date range on the transaction date? If there should be only one cwtc record, then the query could be re-written as:
select cwkt.*; from cwkt; JOIN ctwc ON cwkt.clcode=ctwc.clcode and cwkt.wccode=ctwc.wccode where; funccode="TW" and trndate>={^2017.07.01} and; cwkt.trndate between ctwc.validfr and ctwc.validto; order by clcode,wonbr,trndateOr you could use the greater-than-or-equal, less-than-or-equal syntax, since you no longer have a correlated subquery.
-- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com
[excessive quoting removed by server]