subject:"RE\: SQL Error"

Re: SQL Error

2017-08-09 Thread Laurie Alvey

Thanks to everyone. Never looked at that help page before.

Laurie

On 9 August 2017 at 16:11, Richard Kaye <rk...@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-boun...@leafe.com] On Behalf Of Dave
> Crozier
> Sent: Wednesday, August 09, 2017 11:01 AM
> To: profoxt...@leafe.com
> Subject: RE: SQL Error
>
> Like using "in (select .. ) " which is equally poorly documented.
>
> Dave
>
>
>
[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/camvtr9e7l_hi-mjzntu-czpzk131gutcdp4bfpshfbf1-1y...@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.

RE: SQL Error

2017-08-09 Thread Richard Kaye

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

--

rk

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

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

Dave



___
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/cy4pr10mb176753f61b77eafe8e4cb054d2...@cy4pr10mb1767.namprd10.prod.outlook.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.

RE: SQL Error

2017-08-09 Thread Dave Crozier

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

Dave

-Original Message-
From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Alan Bourke
Sent: 09 August 2017 15:33
To: profoxt...@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. 

-- 
  Alan Bourke
  alanpbourke (at) fastmail (dot) fm

[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/18725b8cd2d5d247873a2baf401d4ab2b478c...@ex2010-a-fpl.fpl.LOCAL
** 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.

Re: SQL Error

2017-08-09 Thread Alan Bourke

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. 

-- 
  Alan Bourke
  alanpbourke (at) fastmail (dot) fm

___
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/1502289159.2870383.1068110536.6e5c3...@webmail.messagingengine.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.

Re: SQL Error

2017-08-09 Thread Laurie Alvey

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.pil...@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 <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 > 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.trndatevalidfr ;
> > 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.trndatevalidfr ;
> > 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.trndatevalidfr ;
> > > 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},
> > > &quo

Re: SQL Error

2017-08-08 Thread Koen Piller

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 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.trndatevalidfr ;
> 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.trndatevalidfr ;
> 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.trndatevalidfr ;
> > 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 *;
> > fr

RE: SQL Error

2017-08-08 Thread Dave Crozier

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.trndatevalidfr 
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.trndatevalidfr ;
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.trndatevalidfr ;
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.trndatevalidfr ;
> 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.trndatevalidfr;
> );
>

Re: SQL Error

2017-08-08 Thread Koen Piller

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.trndatevalidfr ;
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.trndatevalidfr ;
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.trndatevalidfr ;
> 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.trndatevalidfr;
> );
> 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/CACUu1StF9rtK13BH8nmC_iW5mmE7n-v5U=6ufOGhr0N_cc=k...@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.

RE: SQL Error

2017-08-08 Thread Dave Crozier

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.trndatevalidfr ;
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.trndatevalidfr;
);
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/18725b8cd2d5d247873a2baf401d4ab2b478b...@ex2010-a-fpl.fpl.LOCAL
** 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.

Re: SQL Error

2017-08-08 Thread Koen Piller

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.trndatevalidfr ;
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.trndatevalidfr;
);
order By funccode, trndate, clcode, wccode, wonbr Into Cursor temp
gives a result


Koen

2017-08-08 15:20 GMT+02:00 Ted Roche :

> 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.

Re: SQL Error

2017-08-08 Thread Ted Roche

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.

___
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/cacw6n4tu06xfahgyujvqeu9uozkbnictxt2z8ge4kwqwres...@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.

Re: SQL Error

2017-08-08 Thread Laurie Alvey

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  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  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]

___
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/CAMvTR9e5Xkn362TGKs+ZSibSGDEYQUGhY+=xrgarl6xpdzq...@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.

Re: SQL Error

2017-08-08 Thread Ted Roche

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  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

___
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/CACW6n4vR=M=fnm0a9dxmbzuiffu98padr7iz3crq6tzsv_z...@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.

Re: SQL Error

2017-08-08 Thread Michael Madigan

I think Dave is correct, sometimes adding additional parentheses fixes things, 
even though it should work as is.

  From: Dave Crozier <da...@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-boun...@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]

___
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/2058386185.1735488.1502186581...@mail.yahoo.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.

RE: SQL Error

2017-08-08 Thread Dave Crozier

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-boun...@leafe.com] On Behalf Of Gene Wirchenko
Sent: 08 August 2017 01:36
To: ProFox Email List 
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]

___
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/18725b8cd2d5d247873a2baf401d4ab2b478a...@ex2010-a-fpl.fpl.LOCAL
** 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.

RE: SQL Error

2017-08-07 Thread Darren

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-boun...@leafe.com] On Behalf Of Gene
Wirchenko
Sent: Tuesday, 8 August 2017 10:36 AM
To: profoxt...@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]

___
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/005001d30fdf$ebf286e0$c3d794a0$@ozemail.com.au
** 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.

Re: SQL Error

RE: SQL Error

RE: SQL Error

Re: SQL Error

Re: SQL Error

Re: SQL Error

RE: SQL Error

Re: SQL Error

RE: SQL Error

Re: SQL Error

Re: SQL Error

Re: SQL Error

Re: SQL Error

Re: SQL Error

RE: SQL Error

RE: SQL Error

16 matches

Site Navigation

Mail list logo

Footer information