Analytics

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-01-2006
03:28 AM

11-01-2006
03:28 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-01-2006
05:13 PM

11-01-2006
05:13 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-01-2006
08:42 PM

11-01-2006
08:42 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-02-2006
02:13 AM

11-02-2006
02:13 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-02-2006
03:41 AM

11-02-2006
03:41 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-03-2006
01:07 PM

11-03-2006
01:07 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-14-2013
03:17 AM

03-14-2013
03:17 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-14-2013
03:35 AM

03-14-2013
03:35 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-14-2013
04:14 AM

03-14-2013
04:14 AM

It works :)

Great thanks for help Ulrich :)

Now I wonder...

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

Once again, thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-14-2013
05:26 AM

03-14-2013
05:26 AM

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