Ordering numbers stored in a char column.
I am using MS Access as front-end to a DB2 UDB table. One of the columns
in that table is defied as char 5. That column stores numbers and
when I order that column the following results are produced:
1
10
11
2
20
21
Is there a way to correct this in the query? i.e:
1
2
10
11
20
21
Thanks in advance for any help.
James.
[406 byte] By [
James] at [2007-11-9 18:51:19]
# 1 Re: Ordering numbers stored in a char column.
Hi James,
You can use ORDER BY INTEGER(MYFIELD), if you are doing a passthrough query.
(In DB2 an integer is +/- 2 billion, a smallint is -32768 to +32767.)
In the query builder you should be able to do the same thing, though the
syntax may vary and the size of an "integer" may be different.
--Greg
James wrote:
> I am using MS Access as front-end to a DB2 UDB table. One of the columns
> in that table is defied as char 5. That column stores numbers and
> when I order that column the following results are produced:
> 1
> 10
> 11
> 2
> 20
> 21
>
> Is there a way to correct this in the query? i.e:
> 1
> 2
> 10
> 11
> 20
> 21
>
> Thanks in advance for any help.
>
> James.
# 2 Re: Ordering numbers stored in a char column.
Hi James,
Don't know what the performance impact is, but you could use the following:
select column
from table
order by int(column);
Zamil
"James" <tbt102@hotmail.com> wrote:
>
>I am using MS Access as front-end to a DB2 UDB table. One of the columns
>in that table is defied as char 5. That column stores numbers and
>when I order that column the following results are produced:
>1
>10
>11
>2
>20
>21
>
>Is there a way to correct this in the query? i.e:
>1
>2
>10
>11
>20
>21
>
>
>Thanks in advance for any help.
>
>James.
zamil at 2007-11-12 0:04:32 >
