At 03:54 2009-12-01, Peter Cushing <[email protected]> wrote:
[snip]
>Why not combine the first two cursors into one:
>
>select funccode,clcode,wccode,max(validtil) as
>maxvt,iif(year(max(validtil))=2009,.t.,.f.) as ismax from cwkf;
> group by funccode,clcode,wccode;
> into cursor mvt
This does not work, because group by must include all columns
that are not aggregates. It was possible to do this in earlier
versions of the Fox, but it is unsafe as which row's values are
selected for the non-grouped, non-aggregate columns is undefined.
Putting ismax in the group by did not work; I got duplications
differing only on ismax (.f. and .t.). Since many charge codes have
versions over the years, I should have expected this.
However, your suggestion did get me thinking. (Thank you.) I
had somehow blindsided myself and missed how to use a join to do
it. Changing only the third select, I came up with:
***** Start of Included Code *****
select funccode,clcode,wccode,max(validtil) as maxvt from cwkf;
group by funccode,clcode,wccode;
into cursor mvt
select funccode,clcode,wccode,max(validtil) as currvt from cwkf;
where validtil>={^2009.01.01} and validtil<={^2009.12.31};
group by funccode,clcode,wccode;
into cursor cvt
select;
cvt.funccode,cvt.clcode,cvt.wccode,cvt.currvt as validtil,;
cvt.currvt=mvt.maxvt as ismax;
from cvt;
join mvt on;
cvt.funccode=mvt.funccode and cvt.clcode=mvt.clcode and;
cvt.wccode=mvt.wccode;
order by cvt.funccode,cvt.clcode,cvt.wccode;
into cursor truefalse
***** End of Included Code *****
Thank you again.
[snip]
Sincerely,
Gene Wirchenko
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** 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.
