>Hi
>
>I have a large table with a status field and I want to select the records for a group os status's.
>the table is indexed on the status
>
>this can be done by
>

lcStatusList = 'AFTHEW'
>SELECT* ;
>FROM mytable ;
>WHERE mytable.status $ ?lcStatusList

>
>Unfortunately, this does not optimize.
>
>Is there any way to fully optimize this query without expanding the Select to something like
>
>

SELECT* ;
>FROM mytable ;
>WHERE mytable.status = 'A' OR ;
>	mytable.status = 'F' OR ;
>	mytable.status = 'T' OR ;
>	mytable.status = 'H' OR ;
>	mytable.status = 'E' OR ;
>	mytable.status = 'W'

You could use the INLIST() function in your WHERE clause but likely that won't optimize either. CORRECTION: in VFP9 the INLIST() option is reported as fully optimized.

Something you could try:

SELECT ;
  * ;
  FROM MyTable ;
  WHERE MyTable.Status IN ("AFTHEW")

What I usually do in cases like this is create a GetWhereClause() function and do something like

lcWhereClause = GetWhereClause("AFTHEW")
* above sets lcWhereClause to "myTable.status = 'A' OR ..."

* Then,
SELECT ;
  * ;
  FROM MyTable ;
  WHERE &lcWhereClause

Using macro substitution like this basically costs nothing, it's called only once during parsing of the SELECT - SQL and not during its actual execution.