Thanks for trying but this needs to run in VFP 6. I get "Error correlating fields" (I get the same error in VFP 8.) I also modified it to meet the requirement that it check only records at or below the specified version.
SELECT * FROM myTable mm ;
WHERE mm.ctrl = tnCtrl ;
AND mm.state = tcState ;
AND NOT mm.deleted ;
AND mm.version = ( ;
SELECT MAX(version) ;
FROM myTable xx ;
WHERE xx.ctrl = mm.ctrl ;
AND xx.version <= tnVersion
AND mm.state = mm.state )>Hi Rich,
>
>Try
>SELECT * FROM myTable mm ;
> WHERE mm.ctrl = tnCtrl ;
> AND mm.state = tcState ;
> AND NOT mm.deleted ;
> AND mm.version = ( ;
> SELECT MAX(version) ;
> FROM myTable xx ;
> WHERE xx.ctrl = mm.ctrl ;
> AND mm.state = mm.state )
>
>>I have a table of locations. For any given ctrl number, I need to determine if there are any active locations in a particular state for a particular version. The version number reflects the version in which the latest activity took place. In looking at the table below, I want to determine if there are any active records for CA.
>>
>> ctrl locat_num version deleted state
>> 42910 1 0 F MT
>> 42910 2 0 F MT
>> 42910 3 2 F CA
>> 42910 3 3 T CA
>>
>>
>>Version 0 should return FALSE (only MT locations)
>>Version 1 should return FALSE (only MT locations)
>>Version 2 should return TRUE (one CA location)
>>Version 3 should return FALSE (CA location was deleted)
>>
>>I can do this in two queries
>>
>>FUNCTION hasState
>>PARAMETERS tnCtrl,tnVersion,tcState
>>
>>SELECT locat_num,MAX(version) AS version ;
>> FROM myTable;
>> WHERE ctrl = tnCtrl AND version <= tnVersion AND state = tcState;
>> INTO CURSOR curXXX
>>
>>SELECT * FROM myTable mm,curXXX xx;
>> WHERE mm.ctrl = tnCtrl;
>> AND mm.locat_num = xx.locat_num;
>> AND mm.version = xx.version;
>> AND NOT mm.deleted
>>
>>llReturn = _TALLY > 0
>>
>>
>>I'd like to get this down to one query, but can't quite get a grasp on how to do it. Any help would be appreciated.
>>
>>Thanks..........Rich
