'UNLOAD TO' vs 'dbaccess'
A user on IDS 9.40 wanted DECIMAL(6,2) values written without decimals: TRUNC(col1,0) showed "35" in dbaccess, but UNLOAD TO always wrote "35.0". Replies explained UNLOAD deliberately writes raw, unformatted data (decimal/money/float values always keep a decimal point, e.g. 0.0), so display formatting from TRUNC isn't preserved. Two workarounds were offered and worked: cast the value to INT (col1::int), or use dbaccess's OUTPUT TO instead of UNLOAD TO to get the screen-formatted result in a file.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
i'm using IDS 9.40.FC2 on HPUX. i have a column in a table that, according to
dbaccess, is of type: decimal (6,2)
the data in a typical record in the column looks like this in dbaccess:
35.00
i'm using the TRUNC() function to get rid of everything to the right of the
decimal point.
SELECT TRUNC(col1, 0) AS truncated
FROM table1;
in dbaccess, i'm getting the desired affect:
35
if i dump it out to a file, using 'UNLOAD TO junk' from dbaccess, in the
resulting file i'm getting the following whether i use TRUNC() or not:
35.0
right now i'm cleaning the file up with sed. but i'd like to know if i'm doing
something wrong or is there a way to have 'UNLOAD TO' respect the TRUNC()
function or configure how 'UNLOAD TO' formats data? 'UNLOAD TO' seems to be
doing something like a TRUNC(col1, 1) on its own.
thanks
Chris
Could you just cast it to integer?
Example:
Select col1::int from tab1
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of CHRIS
NELSON
Sent: Friday, November 12, 2010 2:51 PM
To: ids@iiug.org
Subject: 'UNLOAD TO' vs 'dbaccess' [21940]
i'm using IDS 9.40.FC2 on HPUX. i have a column in a table that, according to
dbaccess, is of type: decimal (6,2)
the data in a typical record in the column looks like this in dbaccess:
35.00
i'm using the TRUNC() function to get rid of everything to the right of the
decimal point.
SELECT TRUNC(col1, 0) AS truncated
FROM table1;
in dbaccess, i'm getting the desired affect:
35
if i dump it out to a file, using 'UNLOAD TO junk' from dbaccess, in the
resulting file i'm getting the following whether i use TRUNC() or not:
35.0
right now i'm cleaning the file up with sed. but i'd like to know if i'm doing
something wrong or is there a way to have 'UNLOAD TO' respect the TRUNC()
function or configure how 'UNLOAD TO' formats data? 'UNLOAD TO' seems to be
doing something like a TRUNC(col1, 1) on its own.
thanks
Chris
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
casting it to an INT works, thanks!
i suspect a value to the tenth is what is being put in the column (by vendor
app).
dbaccess is displaying to the hundredth because the column type is
decimal(6,2), so when unloaded i get whats 'really' in the table.
i just think its wierd that TRUNC() did not affect the output to an unload
file. regardless of whether a 35.0 or 35.00 is whats actually stored, in
either case TRUNC() should spit out a '35|' to an unload file?
casting it works, so i'll use that, but for my own knowledge i'd like to
figure out this behaviour.
thanks
For you own knowledge. Think of this.
Output is for Humans, made readable, unloads are probably not for humans,
no nice columns, using a field separator that is by default an ugly
character. Formatting should be withheld.
In the internals of the machine, and integer is very different from a
decimal, but all decimals that contain a given value look just alike, no
mater how they are printed.
Why would unload format and field?
Granted it does in the case of DATE (and it could do away with this too by
just casting the date to an integer ) but to change unload in this case
would require the change to load too and this would make things very
interesting when attempting to find a date in an unload file.
Just a thought.
From: "CHRIS NELSON" <cnelson@fwps.org>
To: ids@iiug.org
Date: 11/12/2010 05:00 PM
Subject: 'UNLOAD TO' vs 'dbaccess' [21942]
Sent by: ids-bounces@iiug.org
casting it to an INT works, thanks!
i suspect a value to the tenth is what is being put in the column (by
vendor
app).
dbaccess is displaying to the hundredth because the column type is
decimal(6,2), so when unloaded i get whats 'really' in the table.
i just think its wierd that TRUNC() did not affect the output to an unload
file. regardless of whether a 35.0 or 35.00 is whats actually stored, in
either case TRUNC() should spit out a '35|' to an unload file?
casting it works, so i'll use that, but for my own knowledge i'd like to
figure out this behaviour.
thanks
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
I would not expect 'UNLOAD TO' to format the data in the unload file. However,
I would expect the TRUNC() function to format the output of any column it was
used on, regardless of whether its output to a file(unloaded) or to the
screen(dbaccess).
In dbaccess, when I run:
UNLOAD TO '/tmp/junk.unl'SELECT TRUNC(col1, 0) AS col1_truncated
FROM table1;
I get the following in '/tmp/junk.unl':
35.0|
In dbaccess, when I run:
UNLOAD TO '/tmp/junk.unl'
SELECT col1
FROM table1;I get the following in '/tmp/junk.unl':
35.0|
In dbaccess, when I run:
SELECT TRUNC(col1, 0) AS col1_truncated
FROM table1;
I get the following in dbaccess:
35
In dbaccess, when I run:
SELECT col1
FROM table1;I get the following in dbaccess:
35.00
I'm getting what I would expect to see when the output is sent to
screen(dbaccess). I did not expect prepending 'UNLOAD TO /tmp/junk' to the SQL
to change the result set in an output file.
I'm just still unsure about why unloading the data affects use of the TRUNC()
function.
With "UNLOAD TO" number values with data type INTEGER, INT8,
or SMALLINT zero appear as 0, and MONEY, FLOAT, SMALLFLOAT,
or DECIMAL zero is represented as 0.0.
If you expect to see what the output is sent to
screen with dbaccess, then you shuld try with "OUTPUT TO" syntax, like below
eg.:
OUTPUT TO '/tmp/junk.unl'
SELECT TRUNC(col1, 0) AS col1_truncated
FROM table1;
On Sat, Nov 13, 2010 at 8:20 AM, CHRIS NELSON <cnelson@fwps.org> wrote:
> I would not expect 'UNLOAD TO' to format the data in the unload file.
> However,
> I would expect the TRUNC() function to format the output of any column it
> was
> used on, regardless of whether its output to a file(unloaded) or to the
> screen(dbaccess).
>
> In dbaccess, when I run:
> UNLOAD TO '/tmp/junk.unl'> SELECT TRUNC(col1, 0) AS col1_truncated
> FROM table1;
> I get the following in '/tmp/junk.unl':
> 35.0|
>
> In dbaccess, when I run:
> UNLOAD TO '/tmp/junk.unl'
> SELECT col1
> FROM table1;> I get the following in '/tmp/junk.unl':
> 35.0|
>
> In dbaccess, when I run:
> SELECT TRUNC(col1, 0) AS col1_truncated
> FROM table1;
> I get the following in dbaccess:
> 35
>
> In dbaccess, when I run:
> SELECT col1
> FROM table1;> I get the following in dbaccess:
> 35.00
>
> I'm getting what I would expect to see when the output is sent to
> screen(dbaccess). I did not expect prepending 'UNLOAD TO /tmp/junk' to the
> SQL
> to change the result set in an output file.
>
> I'm just still unsure about why unloading the data affects use of the
> TRUNC()
> function.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00163642707ef7c70b0494ea3bd2
I never noticed UNLOAD statement behaved this way, I'm guessing I dont run into DECIMAL data types much. I'm taking data out of one system where its stored as DECIMAL and feeding it to another that wants an INT. I did cast it to an INT, which worked, but I still wanted to know why I was seeing the results I was getting when using UNLOAD. DECIMAL,MONEY Values are unloaded with no leading currency symbol. In the default locale, comma ( , ) is the thousands separator and period ( . ) is the decimal separator. If DBMONEY is set, UNLOAD uses its specified separators (and its currency format for MONEY values). I didnt quite get a 'zero' would be 0.0 from the above description out of the Guide to SQL Syntax manual, especially when formatting functions (ie ROUND, TRUNC) are applied. (still dont really) I appreciate the mention of the OUTPUT TO reporting statement. I think there are two short mentions of the OUTPUT statement in The INFORMIX Handbook. (One right out of the SQL Syntax manual) I would have never thought about it. Thanks again!