I have the same idea, will inform the helpfile autor. Do you have a
proposal ?
Koen

Op di 8 aug. 2017 om 18:08 schreef Dave Crozier <da...@flexipol.co.uk>

> Koe,
> Yes, but the Between() function or SQL Between expression doesn't fit in
> with being a simple expression like X<=Y or X > Y and you  have proved this
> when you split up  the code to read cwkt.trndate<ctwc.validto AND
> cwkt.trndate>validfr which effectively become two "simple expressions".
>
> I guess it must be something to do with how the Between expression is
> parsed, probably because it will create an additional sub query of its own
> internally.
>
> Dave
>
> -----Original Message-----
> From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Koen Piller
> Sent: 08 August 2017 16:58
> To: ProFox Email List <profox@leafe.com>
> Subject: Re: SQL Error
>
> Hi,
>
> Maybe in Visual Studio Net 2003,
>
> However in VFP this works :)
> 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
> tempselect .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
>
>
> 2017-08-08 17:54 GMT+02:00 Dave Crozier <da...@flexipol.co.uk>:
>
> > According to Microsoft Themselves:
> >
> > SQL: Error correlating fields
> > Visual Studio .NET 2003
> >
> > An outer reference can be used only in syntax similar to the following:
> >
> > X = Y
> >
> > Syntax such as X = Y + 1 or X = 5 will produce this error.
> >
> > Dave
> >
> > -----Original Message-----
> > From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Koen
> > Piller
> > Sent: 08 August 2017 16:40
> > To: ProFox Email List <profox@leafe.com>
> > Subject: Re: SQL Error
> >
> > 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/CACUu1Su-j3uovvZ+MNycVhNqioVjJLs=rNP=eecbwvxvgjp...@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.

Reply via email to