Thanks again Helen
This now works as it should.
order by case when (:indexby)=0 then Supp_Name
else lpad(Supp_Code,'0',5)
end
Alan
--
Alan J Davies
Aldis

> OK, ORDER BY [expression] is valid SQL so, as used here, it won't
> cause a problem for the compiler. Logically the second block is
> being coerced to an alphanumeric ordering because that is determined
> by the first block. With the data you are using, you can't switch
> that - it's not possible to coerce the haulier name into any kind of
> numeric ordering! ;-)
>
> Why don't you write a view that delivers a set that can work either
> way for your SP? You would need to define a derived field in the
> view that massages the value of the haulier code into a string with
> left zero-padding, so that the alphanumeric order would mimic the
> numeric order. The IB_UDF function LPAD() can do this for you. Then
> your second block can specify ordering on this derived value. (Or,
> if you don't like the idea of creating this view, you could try to do
> this massaging in the SELECT statement directly).
>
> ./heLen
>