Hi Alexander,

>> [ order by cast suggestion: ]
>>
>>> Yes, tried that. no luck
>>
>> OK, too bad. This one works however:
>>
>> select cast ( my_char_column as <int or float type here> )
>> , ...
>> , ...
>> from mytable
>> order by 1
>>
>> Or define computed columns for the casts and order on those columns
>> when you need to. If you need to do that often, add real numerical
>> columns and populate them in after insert/update triggers.
>
> Unfortunately this will work only if values in selected strings will
> contain only digits. More general solution I think will be store
> such a columns with leading spaces or add them on select using UDF.

Yes, if there are both numerical and non-numerical values in the same
column, the "select cast..." will break, as will the computed colums.
But in a trigger you can do this:
- set the numerical column to CAST ( charcolumn as <numtype> )
- check with "when gdscode convert_error" (or sqlcode -413)
- set the numerical column to NULL, 0, -1 or whatever if the conversion
failed. NULL would be nice because it will show all non-numerical fields
at the end if you order on the numerical column.

If you say "leading spaces" do you mean right-justifying the field? I
didn't think of that, but of course that works. Only in that case the
numerical values may be interspersed with non-numerals, and leading
zeroes will spoil the ordering too:

1
2
4
T
a
z
00
11
27
67
FB
IB
a3
xx
007
0KM
203
666
A18
Ann
SET
ded

etc.


Greetings,
Paul Vinkenoog