Why does the following query not work? I get an error of "SQL:
Error correlating fields.".

select wo1.* from cwko wo1;
where;
wo1.isgwo and wo1.wostatus#"V" and exists;
(;
select wo2.wonbr from cwko wo2;
where;
wo2.wonbr#wo1.wonbr and wo2.isgwo and wo2.wostatus#"V" and;
(;
(wo2.trndtlow between wo1.trndtlow and wo1.trndthi) or;
(wo2.trndthi between wo1.trndtlow and wo1.trndthi) or;
(wo2.trndtlow<wo1.trndtlow and wo2.trndthi>wo1.trndthi);
);
);
order by wo1.clcode,wo1.wonbr

I am looking for the work orders that are GWOs, are not void, and
where there is another such work order such that their trndtlow to
trndthi date ranges overlap.

I have tried several variations to no avail.

Sincerely,

Gene Wirchenko

It's the "exists" part. You need to supply a field name to check
against, most likely:

.... and wol.wonbr exists ( select wonbr .... )

Hi Gene

Otherwise foxpro will interprete the full line like this:
select wo1.* from cwko wo1where ...

Hth
Bernhard Sander

On Fri, 05 Aug 2005 12:33:03 +0800, man-wai chang
<toylet.toylet (AT) gmail (DOT) com> wrote:

Nope. That is not what EXISTS is for. It is not the same as IN.
From the VFP 6 docs:

Example 6 displays FilterCondition in the form of [NOT] EXISTS
(Subquery)

This example checks to see whether at least one row meets the
criterion in the subquery. When the filter condition includes EXISTS,
the filter condition evaluates to true (.T.) unless the subquery
evaluates to the empty set.

EXISTS ;
(SELECT * FROM orders WHERE customer.postalcode =
orders.postalcode)

That is a syntax error.

Sincerely,

Gene Wirchenko

On Thu, 04 Aug 2005 16:51:45 -0700, Gene Wirchenko
<genew (AT) ucantrade (DOT) com.NOTHERE> wrote:

To follow up, I switched to an inner join.

select;
wo1.clcode,;
wo1.wonbr,wo1.wccode,wo1.trndtlow,wo1.trndthi,;
wo2.wonbr,wo2.wccode,wo2.trndtlow,wo2.trndthi;
from cwko wo1;
inner join cwko wo2 on wo1.clcode=wo2.clcode and
wo1.wonbr<wo2.wonbr;
where;
wo1.isgwo and wo1.wostatus#"V" and;
wo2.isgwo and wo2.wostatus#"V" and;
(;
(wo2.trndtlow between wo1.trndtlow and wo1.trndthi) or;
(wo2.trndthi between wo1.trndtlow and wo1.trndthi) or;
(wo2.trndtlow<wo1.trndtlow and wo2.trndthi>wo1.trndthi);
);
order by wo1.clcode,wo1.wonbr

I had forgotten to specify the the client codes be equal.
Without that, it takes quite a while. The idea of the exists was to
get rid of the non-matches quickly. Fortunately, the optimiser
handled the above well (quickly).

I am still puzzled as to why the exists would not work. I did
get exists to work with a simple correlated query of identity.

Thanks to those who replied.

Sincerely,

Gene Wirchenko

Forgot that... thanks.

--
.~. Might, Courage, Vision. http://www.linux-sxs.org
/ v \
/( _ )\ Linux 2.4.31
^ ^ 2:38pm up 2:06 0 users 1.03 0.86

Correlated subqueries with a that kind of search conditions are not going to
work. A shortcoming.
You can use a join

select wo1.* from cwko wo1, cwko AS wo2.;
where;
wo1.isgwo=.T. and wo1.wostatus#"V" and ;
wo2.wonbr#wo1.wonbr and wo2.isgwo=.T. and wo2.wostatus#"V" and;
( wo2.trndtlow between wo1.trndtlow and wo1.trndthi or;
wo2.trndthi between wo1.trndtlow and wo1.trndthi or;
(wo2.trndtlow<wo1.trndtlow and wo2.trndthi>wo1.trndthi));
order by wo1.clcode,wo1.wonbr

-Anders

Den 05-08-05 01.51, i artikeln v6a5f116jq93nufrirgts7tn9d29gao0r4 (AT) 4ax (DOT) com,
skrev "Gene Wirchenko" <genew (AT) ucantrade (DOT) com.NOTHERE>:

The EXISTS predicate does not take a column name. The correlated subquery
following EXISTS does not need a column name either. One usually writes
SELECT id FOM Table1 WHERE EXISTS (SELECT * FROM Table2 ;
WHERE Table1.id=Table2.id )

The EXISTS clause doesn't return a data value, it returns the result of the
WHERE clause in the subquery, trhat is to say .T. Or .F. Even if you write
WHERE EXISTS (SELECT 'Hello there' FROM Table2 WHERE Table1.id=Table2.id)
Gene's query had AND EXISTS (SELECT w2.wonbr FROM cwko wo2 ..
That's a misunderstanding too, but one that doesn't matter because the
SELECTED value is simply ignored in these kind of subqueries.
-Anders


Den 05-08-05 06.33, i artikeln #PnCEbXmFHA.2628 (AT) tk2msftngp13 (DOT) phx.gbl, skrev
"man-wai chang" <toylet.toylet (AT) gmail (DOT) com>:

Hi Genew
I see you found the solution yourself. I bet the ( ) are superflous around
BETWEEN predicates.

-Anders


Den 05-08-05 20.02, i artikeln th97f1h6c9m747shkb6mpsi33p36etivth (AT) 4ax (DOT) com,
skrev "Gene Wirchenko" <genew (AT) ucantrade (DOT) com.NOTHERE>:

Anders wrote:
thx. I mixed it with "IN".

--
.~. Might, Courage, Vision. http://www.linux-sxs.org
/ v \
/( _ )\ Linux 2.4.31
^ ^ 6:30pm up 5:58 0 users 0.61 0.51