I don't recall if I've ever run into an error 1801. You go, Gene!


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
     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

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: 
** 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