I think Dave is correct, sometimes adding additional parentheses fixes things, even though it should work as is.
From: Dave Crozier DaveC@Flexipol.co.uk To: ProFox Email List profox@leafe.com Sent: Tuesday, August 8, 2017 3:46 AM Subject: RE: SQL Error
Gene, Have you tried bracketing the statement off:
where; (cwkt.clcode=ctwc.clcode) ; and (cwkt.wccode=ctwc.wccode) ; and( cwkt.trndate between ctwc.validfr and ctwc.validto);
Dave -----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Gene Wirchenko Sent: 08 August 2017 01:36 To: ProFox Email List profox@leafe.com Subject: SQL Error
Hello:
The following statement throws "SQL: Error correlating fields.":
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 between ctwc.validfr and ctwc.validto; ); order by clcode,wonbr,trndate
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
The only difference is the second line of the nested select's where expression. The first uses between, and the second uses >= and <=.
What am I missing?
Sincerely,
Gene Wirchenko
[excessive quoting removed by server]