Hi,
Please note:
select .T.; FROM cwkt; where; cwkt.clcode=ctwc.clcode And cwkt.wccode=ctwc.wccode And; (cwkt.trndate BETWEEN ctwc.validfr AND ctwc.validto); order By funccode, trndate, clcode, wccode, wonbr Into Cursor temp
gives a result
and
select .T. ; From ctwc; where; cwkt.clcode=ctwc.clcode And cwkt.wccode=ctwc.wccode And; cwkt.trndate<ctwc.validto AND cwkt.trndate>validfr ; order By funccode, trndate, clcode, wccode, wonbr Into Cursor temp
errors: Column funccode is not found.
queries are identical however query 1 uses between etc and query 2 uses < etc
and query3 with the VFP between function:
select .T.; FROM cwkt; where; cwkt.clcode=ctwc.clcode And cwkt.wccode=ctwc.wccode And; BETWEEN(cwkt.trndate, ctwc.validfr, ctwc.validto); order By funccode, trndate, clcode, wccode, wonbr Into Cursor temp
also gives a result
for you to test yourselve:
CREATE CURSOR cwkt ( funccode C(2), trndate d, clcode c(10), wccode c(10), wonbr c(1)) INSERT INTO cwkt( funccode,trndate,clcode,wccode,wonbr) VALUES ("AB", {^2017.01.01}, "DD", "BB", "1") INSERT INTO cwkt( funccode,trndate,clcode,wccode,wonbr) VALUES ("TW", {^2017.08.01}, "DA", "BA", "2") INSERT INTO cwkt( funccode,trndate,clcode,wccode,wonbr) VALUES ("TW", {^2017.03.01}, "DB", "BC", "3") INSERT INTO cwkt( funccode,trndate,clcode,wccode,wonbr) VALUES ("TW", {^2017.04.01}, "DC", "BD", "4")
CREATE CURSOR ctwc ( clcode c(10), wccode c(10),validfr d, validto d) INSERT INTO CTWC( clcode, wccode, validfr, validto) VALUES ("AC", "BB", {^2017.01.01},{^2017.01.01}) INSERT INTO CTWC( clcode, wccode, validfr, validto) VALUES ("DA", "BA", {^2017.01.01},{^2017.09.01}) INSERT INTO CTWC( clcode, wccode, validfr, validto) VALUES ("DB", "BC", {^2017.01.01},{^2017.02.01}) INSERT INTO CTWC( clcode, wccode, validfr, validto) VALUES ("DC", "BD", {^2017.01.01},{^2017.05.01})
and the full syntax
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; BETWEEN(cwkt.trndate, ctwc.validfr, ctwc.validto); ); order By funccode, trndate, clcode, wccode, wonbr Into Cursor temp
errors: SQL - error correlating fields.
the syntax:
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.validto AND cwkt.trndate>validfr; ); order By funccode, trndate, clcode, wccode, wonbr Into Cursor temp gives a result
Koen
2017-08-08 15:20 GMT+02:00 Ted Roche tedroche@gmail.com:
I think this is referring to something different: a query in the FROM clause is different from a query in the WHERE clause. In the FROM clause, you're using the SQL to create an artificial data source, a "Derived Table" in VFP terms. In the WHERE clause, you're filtering the data in the SELECT portion against a set of tests, which could include running a query and testing it against the source records for matching.
[excessive quoting removed by server]