store in char column getting from decimal column in db2

Question

create table test1 (no decimal(4,2) ,name char(10))

create table test2 (no char(1) ,name char(10))

insert into test1 values(1,'aa') insert into test1 values(2,'ab') insert into test1 values(3,'ac') insert into test1 values(4,'ad') insert into test1 values(null,'ad')

insert into test2 (no,name) (select cast(no as char(1)),name from test1)

not working. any clues.

Thanks.

Leo Leo · Accepted Answer · 2010-11-16T17:08:13

Are you intentionally using CHAR rather than VARCHAR? VARCHAR won't pad your text with spaces.

When I run the insert, I get the following error:

insert into test2 (no,name) (select cast(no as char(1)),name from test1)
SQL0445W  Value "1.00  " has been truncated.  SQLSTATE=01004

The actual insert works for me, but it suggests that you need CHAR(4) or VARCHAR(4) rather than CHAR(1).

If you want to automatically drop the digits after the decimal, you could cast the values to BIGINT first:

insert into test2 (no,name) (
  select 
    cast(cast(no as bigint) as char(1)),
    name
  from test1
)

Note that you will still run into truncation issues for values like 10 or -1.

store in char column getting from decimal column in db2

1 Answers