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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

To: informix-list@iiug.org

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-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]
On Behalf Of R. Spitz
Sent: Thursday, August 04, 2016 12:39 AM
To: informix-list@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
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://members.iiug.org/mailman/listinfo/informix-list

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thursday, August 4, 2016 at 2:23:30 PM UTC+2, Mike Walker wrote:

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

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.

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?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

To: informix-list@iiug.org

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-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]
On Behalf Of R. Spitz
Sent: Thursday, August 04, 2016 7:30 AM
To: informix-list@iiug.org
Subject: Re: to_char() function for integer in dbaccess

On Thursday, August 4, 2016 at 2:23:30 PM UTC+2, Mike Walker wrote:

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

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.

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?
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://members.iiug.org/mailman/listinfo/informix-list

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)