>All,
>
>I'm using SQL Select to retrieve data from a few tables where one of the field in the cursor is "month".
>
>Example,
>03/98
>04/98
>06/98
>02/99
>06/99
>
>For that, I need to put the order as,
>ORDER BY SUBSTR(month,4,2),SUBSTR(month,1,2)
>
>However, this cannot be done because ORDER BY need to refer to a field, but not SUBSTR(...).
>Any solution or good idea to this??

You can always create another field in the output cursor that contains the sort expression, and reference that in your ORDER BY clause, or you can build an index after the SELECT completes to order the output for you.

Approach 1:

Select <i>list of desired fields</i>, ;
 SUBSTR(month,4,2) + SUBSTR(month,1,2) AS SortFld ;
 FROM ...
 ORDER BY SortFld

Approach 2:

SELECT <i>the remainder of your SQL Select statement</i>
INDEX ON SUBSTR(month,4,2) + SUBSTR(month,1,2) TAG Temp
GO TOP && reposition the record pointer to make the index take effect

A friendly warning here - you're exposing yourself to potential Y2K issues in the application, and it's showing here in just this tiny fragment of the application! If the date information is stored as character expressions, you've already created a long-term problem - the data in the database is going to be bit on the butt by Y2K, and you've got less than 6 months before the problem strikes (in fact, if you project dates into the future, it's probably already too late.) I'd recommend a little reworking of how the data is stored now, rather than trying to fix things after your app starts spurting blood and guts in January!