You can (now) sort by a column that is not in the select list, so you can do
your cast to a character and still have an "order by" that references your
numeric column, so that it sorts correctly. The cast to a char does make
the column left justified though. The to_char leaves the numbers right
I guess the question though, is if you have a numeric column already, then
why cast it to a char in the first place? Is it just that you're trying to
reduce the size to make dbaccess happy? You could try casting to a smallint
- still 6 characters long, but smaller than an integer, and maybe that's
good enough for your 4-digit numbers?
Formatting within dbaccess is a bit limited, and I usually just unload to a
temp file, then format with awk, or something else which allows more precise
formatting of the data, including decimal positions.
From: firstname.lastname@example.org [mailto:email@example.com]
On Behalf Of R. Spitz
Sent: Thursday, August 04, 2016 7:30 AM
Subject: Re: to_char() function for integer in dbaccess
Post by Mike Walker
I say it's a "feature". I don't know what the datatype if returned by
the to_char function, but it's not going to be a single character field.
Very interesting. You seem to be right, but it's rather counterintuitive
when to_char() doesn't return a character field with the length specified in
the format string
Post by Mike Walker
You can achieve what you want by casting the result to a single character,
select nr as n, to_char(nr,"#")::char(1) as c from try
...but that assumes that you know that it will always be a single
I had tried directly casting the integer to char(n) before, without using
to_char() function. That results in correct column display in dbaccess, but
it breaks numerical sorting on the casted column. Casting the result of the
to_char() function solves both problems, as long as there are no negative
numbers involved, so thank you for providing your solution.
In my use case, the actual range of the numbers is from 0 to 9999, so I can
safely assume it will fit into a char(4) and will sort correctly.
Is there any generic solution that will retain numerical sorting while still
reducing the necessary column width of an integer to the actual number of
digits used, so that the results of a query will fit into dbaccess's 80