The following query has been giving me grief. The error I get is
"SQL: Error correlating fields". The docs are less than clear on what
this means.

select *;
from cwko as gsm;
where;
gsm.wccode="GSM" and not exists;
(;
select .f.;
from cwko as notgsm;
where;
notgsm.clcode=gsm.clcode and;
left(dtos(notgsm.trndtlow),6)=left(dtos(gsm.trndtl ow),6) and;
left(dtos(notgsm.trndthi),6)=left(dtos(gsm.trndthi ),6) and;
notgsm.wccode#"GSM";
);
order by
gsm.clcode,gsm.trndtlow,gsm.trndthi,gsm.wccode,gsm .wonbr

I am looking for all work orders with a wccode of "GSM" where
there is no work order for that month for that client with a wccode of
other than "GSM".

The VFP 9 beta throws the same error.

I tried other things including calculated columns, but any way
that I have gone about it, the subquery throws an error of some sort.

What am I missing?

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Gene Wirchenko schrieb:
This "not exists" looks odd to me; is this a valid SQL construct? Can
you try it with something like

....and gsm.ID not in (select gsm.ID...

or maybe a

{Query 1}
minus
{Query 2}

(too lazy to check if VFP supports "minus")?

Martin

Martin Gebert schrieb:

Oops, sorry, I should have said "'not exists' in combination with
'select .f.' looks odd". Try selecting a column in your subquery.

And this, of course, should read "...(select notgsm.ID..." :-(

Excuse me, it's still too early in the day...

Martin

Gene and Anders:
I've been following this as best I can, and I might be looking at this
wrong, but here's what I see:

It seems that you both have been discussing what occurs in the 'where'
clause in the subquery. But I see the result of Gene's subquery as being
the problem. It looks to me that the subquery would return a single column
of .f. with a field name of I-can't-imagine-what, which fox would not be
able to line up with the main query. But Ander's last post, where the
subquery starts with 'select *' would give valid fields that fox could align
with the main query.

Run _just_ the subquery below, and I think you will see what I mean.

Standard disclaimers....
--
Bill Browne
www.edgefinderstudios.com


"Gene Wirchenko" <genew (AT) mail (DOT) ocis.net> wrote

"Bill Browne" <bill (AT) excalibur-dbf (DOT) com> wrote:

It does not have to "line up" the two queries. I am using the
exists operator with the subquery. That operator returns true or
false. What is returned by the subquery is irrelevant, except for the
question whether there is anything at all. I use select .f. because
it will create a smaller result.

Where? What? The above is the last line of your post.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.