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,trndate
Or you could use the greater-than-or-equal, less-than-or-equal syntax, since you no longer have a correlated subquery.