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 <tedro...@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]
_______________________________________________
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:
http://leafe.com/archives/byMID/profox/cacuu1ssce52rro4pfwza-r_kg3o3-maycrf7fpkgrmu3xbd...@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.
