>>>>>>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 :-)

But of course. I'm assuming here they're all alpha-numeric :-)