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 <[email protected]> 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 _______________________________________________ Post Messages to: [email protected] 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/CACW6n4vR=M=fnm0a9dxmbzuiffu98padr7iz3crq6tzsv_z...@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.
