CODEWITHSUNDEEP
databasedb2db2-luw
TheGoblinPopper
TheGoblinPopper

Reputation: 149

How do you change a DECFLOAT to an INT using DB2 SQL?

simply put I am trying to get rid of all of these DECFLOAT types in my database as a lot of reporting tools (such as Tableau) don't want to work with them. I am new to DB2 and have found a lot of literature online to go from any data type -> DECFLOAT, but nothing substantial about going from DECFLOAT -> INT.

Is this even possible? Any recommendations?

Upvotes: 1

Views: 2147

Answers (2)

Paul Vernon
Paul Vernon

Reputation: 3901

I agree with @Used_by_Already that you might be best updating your BI tools so that they work with DECFLOAT, or use VIEWs to convert to INT or DECIMAL (or even FLOAT !?) on the fly.

But if you do want to change the data type of an existing table, you can do that (with some restrictions), as the example below shows

create table df(df decfloat(16) not null primary key) organize by row;
insert into df values 1.0, 2.00, 3.120;
select * from df;

gives

DF                      
------------------------
                     1.0
                    2.00
                   3.120

  3 record(s) selected.

then

alter table df alter column df set data type integer;

returns

SQL0478N  The statement failed because one or more dependencies exist on the 
target object.  Target object type: "COLUMN". Name of an object that is 
dependent on the target object: "SQL180924130131030". Type of object that is 
dependent on the target object: "PRIMARY KEY".  SQLSTATE=42893

so 

alter table df drop primary key;
alter table df alter column df set data type integer;

then

select * from df

gives

DF         
-----------
          1
          2
          3

  3 record(s) selected.

but 

insert into df values 4

fails due to reorg pending

SQL0668N  Operation not allowed for reason code "7" on table "PAUL.DF".  
SQLSTATE=57016

so

call admin_cmd('reorg table df');
insert into df values 4;
alter table df add primary key (df);
select * from df;

gives

DF         
-----------
          1
          2
          3
          4

  4 record(s) selected.

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35603

Casting DECFLOAT to integer is supported according to Table 2. Supported casts between built-in data types and in fact you can achieve conversion to any of these: SMALLINT, INTEGER, BIGINT, DECIMAL, DECFLOAT, REAL, DOUBLE, CHAR, VARCHAR (depending on the data, e.g. there are limits to how large a smallint can be).

But why would you alter the table columns to integer? Why not DECIMAL instead? Or, don't alter your tables to suit BI products, instead use views that cast to decimal. You could also control rounding through views.

Whilst today's BI tools may have issues with this data type, that data type is based on standard EEE754r and in future versions of BI tools it may be less of a problem. Perhaps read DECFLOAT: The data type of the future before altering any tables.

Upvotes: 3

Related Questions