All Forums Analytics
bhaskaran 9 posts Joined 05/05
01 Nov 2006
conversion of datatype

Need help for the below questionI 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 tabletable y(xyz integer,abc varchar(50))Insert into table ySel xyz,sum(abc)Fromtable xgroup by 1;When I populate the table y the value has been populated in exponential format.Example:value from sel is : 41900000where as it is populated as 4.19000000000000E 007 Kindly let me know as how to solve this problem

Fred 1096 posts Joined 08/04
01 Nov 2006

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 ySel xyz,sum(CAST(abc as DECIMAL(18,2)))Fromtable xgroup 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.

bhaskaran 9 posts Joined 05/05
01 Nov 2006

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

XTUPIE 42 posts Joined 11/05
02 Nov 2006

HiYou 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.

Regards
Divvy

bhaskaran 9 posts Joined 05/05
02 Nov 2006

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

Fred 1096 posts Joined 08/04
03 Nov 2006

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.

wicik 35 posts Joined 06/12
14 Mar 2013

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?
 

ulrich 816 posts Joined 09/09
14 Mar 2013

Don't post many times the same question. Solution was given at
Numeric Overflow error in teredata

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

wicik 35 posts Joined 06/12
14 Mar 2013

It works :)
 
Great thanks for help Ulrich :)
 
Now I wonder...
Cast all the scripts or alter main and target tables...
 
Once again, thanks!

ulrich 816 posts Joined 09/09
14 Mar 2013

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.