conversion of datatype

Analytics

conversion of datatype

Need help for the below question

I have numerical column, but that has been changed to VARCHAR(21)

table structure:
table x
(
xyz integer,
abc varchar (21) -- this was previously a numeric column with dec(18,2)
)

I am selecting the values from the above table and inserting it into a target table

table y
(
xyz integer,
abc varchar(50)
)

Insert into table y
Sel xyz,sum(abc)
From
table x
group by 1;

When I populate the table y the value has been populated in exponential format.

Example:

value from sel is : 41900000

where as it is populated as 4.19000000000000E 007

Kindly let me know as how to solve this problem
9 REPLIES
Teradata Employee

Re: conversion of datatype

Looks like the implicit conversion from VARCHAR to a numeric type (required for SUM) is using FLOAT.

First step would be to explicitly CAST the VARCHAR back to DECIMAL(18,2). So it could be as simple as:

Insert into table y
Sel xyz,sum(CAST(abc as DECIMAL(18,2)))
From
table x
group by 1;

But I notice your target field is wider than 21 characters, and Teradata is currently limited to at most 18 digits for native numeric types. If your SUMs can be larger than that it will be more complicated.

Re: conversion of datatype

Yes I know that. I tried what you have suggested. It seems they will get more than 18 digits from the source. Can you provide me the solution
N/A

Re: conversion of datatype

Hi

You can only cast to Decimal(18,?) so if your numbers are expected to be up to 18 then is fine. Otherwise you can try casting to a character or some other numeric type.

Re: conversion of datatype

Kindly let me know to which data type should I cast it. I tried using FLOAT, but the values are rounded.
Teradata Employee

Re: conversion of datatype

Teradata does not currently have a native type that supports exact numeric values with over 18 digits precision.

This would be a good application for a UDT and an aggregate UDF that computes a "SUM" for fields of that type. One could also omit the UDT and write a UDF that accepts [VAR]CHAR representations of numbers and returns [VAR]CHAR.

If you're looking for a SQL-only solution, it will be messy. You may have to do something like: SUBSTRING to break the values into two or more fields, such that after CASTing to numeric and SUMming you won't have overflow for any of the pieces; then deal with "carries" from lower-order to higher-order parts of the result; then CAST and concatenate the final results back to a single VARCHAR.
N/A

Re: conversion of datatype

Hi there

Unfortunetly I have problem with data conversion  ;P

select

   tab_id

  ,tab2

  ,tab3

  ,tab_id_2

  ,tab4

  ,tab5

  ,sum(data_table_uplink)    as total_data_table_uplink

  ,sum(data_table_downlink)  as total_data_table_downlink

  ,sum(data_table_UPLINK)     as total_data_table__UPLINK

  ,sum(data_table_DOWNLINK)   as total_data_table__DOWNLINK

  ,sum(data_table__DURATION)      as total_data_table__DURATION

 from DB_table.SomeTable

 group by 1,2,3,4,5,6

It is a part of bigger select but this part returns me  Select Failed. 2616: Numeric overflow occured during computation.

How to convert it to another data so select result will return me some proper data which I will be able to insert to another table?

N/A

Re: conversion of datatype

Don't post many times the same question. Solution was given at

Numeric Overflow error in teredata

N/A

Re: conversion of datatype

It works :)

Great thanks for help Ulrich :)

Now I wonder...

Cast all the scripts or alter main and target tables...

Once again, thanks!

N/A

Re: conversion of datatype

The change might have implicartion on the required space to store the data which need to be considered.