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 <tedro...@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 <ge...@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,trndate
>
> Or 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]

_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CAMvTR9e5Xkn362TGKs+ZSibSGDEYQUGhY+=xrgarl6xpdzq...@mail.gmail.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.

Reply via email to