CODEWITHSUNDEEP
sqltype-conversionamazon-redshift
AndyDas1
AndyDas1

Reputation: 1

Conversion issue from string to decimal in redshift

getting below error while loading data in redshift table. Source column has string as data type, and target has decimal(7,2) as datatype.

Sample record of source:-

TOTAL_HOURS | REGULAR_HOURS
30.299997   | 30.299997
28.25       | 27.75
2.566666    | 2.566666

Error :-

SQL Error [500310] [XX000]: Amazon Invalid operation: Numeric data overflow (result precision)

Query that I am trying :- I have tried with numerous options however I keep getting the above error . Few queries that I tried are :-

1).

INSERT INTO schema.target_tbl 
SELECT  to_number(Total_hours ,'9999D99')  As Total_hours,
    to_number(Regular_hours ,'9999D99')  As Regular_hours,
FROM schema.source_tbl

2).

INSERT INTO schema.target_tbl 
SELECT
cast(Total_hours As float) As Total_hours,
cast(Regular_hours As float) As Regular_hours,

3).

INSERT INTO schema.target_tbl 
SELECT
cast(cast(Total_hours As Float) As decimal(7,2)) As Total_hours,
cast(cast(Regular_hours As Float) as  decimal(7,2)) As Regular_hours,

Upvotes: 0

Views: 6548

Answers (1)

Shailesh
Shailesh

Reputation: 2286

I created two tables, like yours:

create table decimal_test1(val varchar);
create table decimal_test2(val DECIMAL(7,2));

insert into decimal_test1 values('30.299997');

insert into decimal_test2 select val from decimal_test1; --works
insert into decimal_test2 select to_number(val ,'9999D99') from decimal_test1; --also works

Both the last two queries are giving the expected results and inserting data into the table.

Upvotes: 0

Related Questions