Re: SQL Error

9 Aug 2017


      I've only ever used EXISTS in T-SQL. Didn't know it could be used in VFP.
It doesn't seem to be documented anywhere - what about SOME or ANY?
Laurie
On 8 August 2017 at 19:04, Koen Piller koen.piller@gmail.com wrote:
...
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 DaveC@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-bounces@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 DaveC@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-bounces@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 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]

2026

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

Re: SQL Error