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]

Don't know if this helps but the VFP9 SP2 help file says this:

A sub-SELECT is often referred to as a derived table. Derived tables are SELECT statements in the FROM clause referred to by an alias or a user-specified name. The result set of the SELECT in the FROM clause creates a table used by the outer SELECT statement. Visual FoxPro 9.0 permits the use of a subquery in the FROM clause.

A sub-SELECT should be enclosed in parentheses and an alias is required. Correlation is not supported. A sub-SELECT has the same syntax limitations as the SELECT command, but not the subquery syntax limitations. All sub-SELECTs are executed before the top most SELECT is evaluated.

The following is the general syntax for a subquery in the FROM clause.

SELECT … FROM (SELECT …) [AS] Alias

So it seems that a sub-query MUST have an alias.

Laurie

On 8 August 2017 at 11:54, Ted Roche tedroche@gmail.com wrote:

I don't recall if I've ever run into an error 1801. You go, Gene!

https://msdn.microsoft.com/en-us/library/aa976358(v=vs.71).aspx

Seems to indicate that subqueries don't support the syntax. Note that's an old MSDN entry, so 9.0 could be better.

On Mon, Aug 7, 2017 at 8:35 PM, Gene Wirchenko genew@telus.net wrote:

...

Hello:

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

Is there likely to be more than one ctwc record with with a valid date range on the transaction date? If there should be only one cwtc record, then the query could be re-written as:

select cwkt.*;
from cwkt;
JOIN ctwc
ON  cwkt.clcode=ctwc.clcode and cwkt.wccode=ctwc.wccode
where;
 funccode="TW" and trndate>={^2017.07.01} and;
 cwkt.trndate between ctwc.validfr and ctwc.validto;
order by clcode,wonbr,trndate

Or you could use the greater-than-or-equal, less-than-or-equal syntax, since you no longer have a correlated subquery.

-- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com

[excessive quoting removed by server]

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]

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]

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]

On Wed, 9 Aug 2017, at 03:21 PM, Laurie Alvey wrote:

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?

it's on the "Filter Conditions for Queries and Views" help file page. SOME and ANY also.

Tamar needs a new pair of shoes. Buy her book - http://www.hentzenwerke.com/catalog/tamingvfpsql.htm :-)

--

rk

-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Crozier Sent: Wednesday, August 09, 2017 11:01 AM To: profoxtech@leafe.com Subject: RE: SQL Error

Like using "in (select ...... ) " which is equally poorly documented.

Dave