>>>>>i want to create a sql statement for character field with index.
>>>>>
>>>>>sele accode, billdate, billno, amt from xcash2 group by accode, billno into curs ccust order by billdate,billno
>>>>>
>>>>>billno is a character field.
>>>>>
>>>>>this showing billnos
>>>>>1
>>>>>10
>>>>>11
>>>>>12
>>>>>13
>>>>>14
>>>>>15
>>>>>16
>>>>>17
>>>>>18
>>>>>19
>>>>>2
>>>>>20
>>>>>21
>>>>>
>>>>>but i want to see like :
>>>>>1
>>>>>2
>>>>>3
>>>>>4
>>>>>5
>>>>>6
>>>>>
>>>>>
>>>>>warm regards,
>>>>>mk.
>>>>
>>>>

>>>>SELECT *, PADL(ALLTRIM(billno),6,[0]) AS OrdBy FROM ..... ORDER BY OrdBy
>>>>

>>>
>>>Hi BB
>>>
>>>Wouldn't that still be in the same order - OrdBy still being char data?
>>>I bow to you superior SQL knowledge, but I would have used:
>>>
>>>SELECT *, FROM ..... ORDER BY OrdBy
>>>
>>>Terry
>>
>>No, because this char field is left-padded with [0], you have:
>>[00001]
>>[00002]
>>[00003]
>>...
>>in OrdBy field.
>
>Ah yes, but wouldn't VAL( BillNo) AS OrdBy work just as well?


It would work also :-), If all data in that field is numeric, but if you have something like:
[A12]
[B73]
VAL() will return zero and these records will be first :-)