to_char() function for integer in dbaccess

[R. Spitz]

Hi Informixers,

the following little sql scripts results in unexpected formatting of output columns in dbaccess:

create table try(nr integer);
insert into try(nr) values(1);
select nr as n, to_char(nr,"#") as c
from try

Since the to_char function results in a column that's only 1 character wide, the result of the query should fit within the default 80 columns of dbaccess and display in one line with two columns. However, dbaccess interprets it as "too wide" and displays the result in two lines.

I'm getting this result with "DB-Access Version 12.10.FC1WE" and "DB-Access Version 12.10.FC3WE", both running on SLES 11 SP4. Bug or feature?

Regards, Richard

[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.

You can achieve what you want by casting the result to a single character,
e.g:

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 character
field.

Mike


-----Original Message-----
From: informix-list-***@iiug.org [mailto:informix-list-***@iiug.org]
On Behalf Of R. Spitz
Sent: Thursday, August 04, 2016 12:39 AM
To: informix-***@iiug.org
Subject: to_char() function for integer in dbaccess

Hi Informixers,

the following little sql scripts results in unexpected formatting of output
columns in dbaccess:

create table try(nr integer);
insert into try(nr) values(1);
select nr as n, to_char(nr,"#") as c
from try

Since the to_char function results in a column that's only 1 character wide,
the result of the query should fit within the default 80 columns of dbaccess
and display in one line with two columns. However, dbaccess interprets it as
"too wide" and displays the result in two lines.

I'm getting this result with "DB-Access Version 12.10.FC1WE" and "DB-Access
Version 12.10.FC3WE", both running on SLES 11 SP4. Bug or feature?

Regards, Richard

[R. Spitz]

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 character
field.

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 columns?

[Mike Walker]

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
justified.

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.

Mike



-----Original Message-----
From: informix-list-***@iiug.org [mailto:informix-list-***@iiug.org]
On Behalf Of R. Spitz
Sent: Thursday, August 04, 2016 7:30 AM
To: informix-***@iiug.org
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
character field.

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
columns?