>>>hello everybody,
>>>
>>>in a table I have a field named CustType, which can only have 4 values: Q, W, E, R
>>>
>>>I want to sort the table by CustType, but neither in ascending(EQRW) nor descending(WRQE) order, I want the order to be Q,W,E,R. Is it possible?
>>>
>>>Are there any VFP commands can do this?
>>>Is it possible to sort the table in this order, when I'm selecting data with SQL statement?
>>>Which way is better?
>>>
>>>Thanks a lot!!
>>>Jimi
>>
>>I would use a UDF:
>>
>>

>>function CustomerSort(tcType)
>>do case
>>case tcType = "Q"
>>  return "1"
>>case tcType = "W"
>>  return "2"
>>...
>>

>>
>>Another possibility is nested IIFs in the SELECT statement itself.
>>
>>Hilmar.
>
>Yeah it's exactly what I'm doing now :)

Well, since several nested IIFs quickly become unreadable, I would recommend the UDF, or the table approach you got in your other response, if you need this particular sorting more than once.

I forgot to mention missing or invalid values.

The DO CASE should finish with something like:

OTHERWISE
  return "X"
  * or return "0", to send to the beginning
ENDCASE

And the table approach should use an outer join.

Unless you validate for missing or invalid values at the table level (triggers), it is very likely that you get invalid values sooner or later.

Hilmar.

Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)