I am making some fairly severe modifications to an app I have
written. I want to do some checks on the data before I make the
changes.

One of the checks I wanted to make was comparing certain similar
work orders. I wanted to do it in one SQL query, but I could not
figure out how. I ended up querying to find where there was more than
one of the type of work order and then iterating through that to get
the individual work orders. My code follows:

set talk off

select;
clcode,wccode,;
left(dtos(trndtlow),6) as low,left(dtos(trndthi),6) as high,;
count(*) from cwko;
group by clcode,wccode,low,high;
where wostatus#"V";
having count(*)>1;
into cursor mults

select mults
goto top
do while !eof()

select * from cwko where;
clcode=mults.clcode and wccode=mults.wccode and;
left(dtos(trndtlow),6)=mults.low and;
left(dtos(trndthi),6)=mults.high

select mults
skip

enddo

use in mults

return

Checking and closing each browse is awkward as is joining the
subqueries together. Is there any way to do this in one SQL query?

Sincerely,

Gene Wirchenko

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

Hi Gene,

At first glance, I thought this was a self-join candidate but
on closer examination, I don't think it can be done with
a single query.

However, having established the number of "duplicates" in
cursor "mults", you could simply obtain a cursor containing all the
"duplicates" by running a second query joining mults to cwko.

Something like ...

Select * from mults join cwko;
on mults.clcode=cwko.clcode ;
and mults.wccode=cwko.wccode ;
and mults.low=left(dtos(cwko.trndtlow),6);
and mults.high=left(dtos(cwko.trndthi),6) ;
where cwko.wostatus<>"V" ;
into cursor c_dupes

If I'm missing the point, please feel free to call me an idiot ;-)

-Roger
--
Roger Ansell
Adelaide, Australia

My real email address is ransell at senet dot com dot au

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

"Roger Ansell" <notmyreal (AT) emailaddress (DOT) com> wrote:

I was thinking so. I believe it could be done in one query if
VFP supported
select ... from (<subquery>)

That would be rude, and no, you did get it.

A few quibbles:

1) "high" is a reserved word, so I changed it. This was my error.

2) There should be an order clause.

3) I did not want any of the columns from mults to show. I managed
to solve that one with a bit of thought.

I ended up:

select cwko.* from mults;
join cwko;
on mults.clcode=cwko.clcode and mults.wccode=cwko.wccode and;
mults.dlow=left(dtos(cwko.trndtlow),6) and;
mults.dhigh=left(dtos(cwko.trndthi),6);
where cwko.wostatus#"V";
order by mults.clcode,mults.wccode,mults.dlow,mults.dhigh;
into cursor c_dupes

Thank you for the help.

Sincerely,

Gene Wirchenko

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

Untested speculation, but it seems like something like this has worked for
me in the past:

Assuming all your fields are fixed length strings (apply str() if not)

select * from cwko where;
clcode + wccode;
+ left(dtos(trndtlow),6) + left(dtos(trndthi),6)
in;
(select;
clcode + wccode;
+ left(dtos(trndtlow),6) + left(dtos(trndthi),6) as anything, ;
count(*) from cwko;
group by anthing;
where wostatus#"V";
having count(*)>1)

--
Bill Browne
www.edgefinderstudios.com


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

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

clcode and wccode are fixed-length strings.

Thank you. Interesting solution. I had not thought of
concatenating the fields. It is ugly though. (I prefer to avoid
mixing domains where possible.) Can you think of a way that does not
involve the concatenation? To me, it does not appear possible. That
aggregation makes the difference. I do wish VFP supported a
select ... from <subquery>

I ran this problem by the instructor I had for my databases
course, and he did not see any better. I wonder what he would think
of your approach.

Sincerely,

Gene Wirchenko

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

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

Have you checked out the VFP9 beta? It has exactly what you're looking for.

http://msdn.microsoft.com/vfoxpro

Fred
Microsoft Visual FoxPro MVP

Would probably send him screaming into the night. But if it works...

--
Bill Browne
www.edgefinderstudios.com


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