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
Maybe try .. between(cwkt.trndat, ctwc.validfr, ctwc.validto) . yes I know - better not to use but maybe worth a try.
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Gene Wirchenko Sent: Tuesday, 8 August 2017 10:36 AM To: profoxtech@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]
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]
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]
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.
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,trndateOr 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]
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.
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]
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]
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]
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]
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]
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]
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.
Like using "in (select ...... ) " which is equally poorly documented.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Alan Bourke Sent: 09 August 2017 15:33 To: profoxtech@leafe.com Subject: Re: SQL Error
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
Thanks to everyone. Never looked at that help page before.
Laurie
On 9 August 2017 at 16:11, Richard Kaye rkaye@invaluable.com wrote:
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
[excessive quoting removed by server]