Database
Enthusiast

## How to Use Round Function In Teradata

Hello everyone,

I have a question about Round function conversion of SQL Server Query to Teradata Query.

If anyone can help me in conversion of query to teradata;

COLUMNNAME = CONVERT ( DECIMAL (27,7) , isnull ( ( ROUND ( COLUMNNAME , 2) ) ,0 ))

Best Regards;

Ismail Arslan

9 REPLIES
Junior Contributor

## Re: How to Use Round Function In Teradata

Hi Ismail,

ISNULL -> COALESCE

CONVERT -> CAST

ROUND -> might exist as a UDF on your system, otherwise it translates to a CAST in your case:

CAST(CAST(COALESCE(col, 0) AS DECIMAL(27,2)) AS DECIMAL(27,7)) AS col

Dieter

Enthusiast

## Re: How to Use Round Function In Teradata

thank you very much Dieter.

See you later.

Best Regards;

Ismail Arslan

## Re: How to Use Round Function In Teradata

Hello,

1. SELECT CAST((1.2435) AS DECIMAL (5,3)); result - 1.244

2. SELECT CAST((1.2445) AS DECIMAL (5,3)); result - 1.244

3. SELECT CAST((1.2455) AS DECIMAL (5,3)); result - 1.246

I expected the result of 2nd query to be 1.245 but it is not.

Senior Supporter

## Re: How to Use Round Function In Teradata

The keyword is RoundHalfwayMagUp

its a dbscontroll setting and defines how Teradata is handling the rounding.

Default is FALSE

from the manuals:

Purpose

Indicates how rounding should be performed when computing values of DECIMAL type. A

halfway value is exactly halfway between representable decimal values.

Field Group

General

Valid Settings

The rounding behavior is different depending upon the setting of the RoundHalfwayMagUp

field.

Default

FALSE

Changes Take Effect

After the next Teradata Database restart.

IF you set the field to… THEN the Teradata Database system uses the rounding semantics…

TRUE appropriate for many business applications:

The magnitudes of halfway values are rounded up. Halfway values are

rounded away from zero so that positive halfway values are rounded up

and negative halfway values are rounded down (toward negative infinity).

For example, a value of 2.5 is rounded to 3.

A halfway value is rounded up or down so that the least significant digit is

even. For example, a value of 2.5 is rounded to 2.

Enthusiast

## Re: How to Use Round Function In Teradata

Hi Team,

Oracle Query :-

select ROW_WID,

ROUND( ( W_ORDER_D.X_ATTRIB_27 - ( SYSDATE + ( 330 / 1440 ) ) ) * 24 ) ,

ROUND( W_ORDER_D.X_ATTRIB_27 - SYSDATE),

W_ORDER_D.X_ATTRIB_27 - SYSDATE

FROM W_ORDER_D

WHERE W_ORDER_D.ORDER_TYPE = 'Service Order' ;

How to convert above mentioned query into TD (Using 13.10), There is no ROUND function in teradata for same.

Junior Contributor

## Re: How to Use Round Function In Teradata

Hi Mahesh,

there's ROUND in TD14...

In your case you don't need ROUND as a CAST AS DECIIMAL(xx,0) should return the same result.

But i assume W_ORDER_D.X_ATTRIB_27 is a TIMESTAMP and then it's probably more complicated, depends on what you actually want. Do you already have a replacement for those calculations?

SYSDATE + ( 330 / 1440 ) -> SYYDATE + INTERVAL 330 MINUTE

W_ORDER_D.X_ATTRIB_27 - SYSDATE will return an INTERVAL, e.g. DAY(4) TO SECOND

Dieter

Enthusiast

## Re: How to Use Round Function In Teradata

Thanks for quick response.

Select ROW_WID,

X_ATTRIB_27,

SYSDATE,

W_ORDER_D.X_ATTRIB_27 - SYSDATE---> Gives in days

FROM W_ORDER_D

WHERE W_ORDER_D.ORDER_TYPE = 'Service Order'

AND ROW_WID IN (11010,11011) ;

ROW_WID,   X_ATTRIB_27,                        SYSDATE ,             Difference Gives in days

11010    6/30/2004 10:55:20 AM 5/4/2013 3:04:32 PM       -3230.17305555556

11011    6/30/2004 10:56:04 AM                5/4/2013 3:04:32 PM         -3230.1725462963

In Teradata we’ve a limitation that it will NOT be given more 9999 values.

We required output as if it was in oracle, Can you please give me any UDF it gives us difference in exactly days and should allow more than 9999 values.

Enthusiast

## Re: How to Use Round Function In Teradata

`Thanks for quick response (sorry :( above query-->oracle)ORACLE QUERY:-SELECT  ROW_WID, X_ATTRIB_27, SYSDATE, ROUND( ( W_ORDER_D.X_ATTRIB_27 - ( SYSDATE + ( 330 / 1440 ) ) ) * 24 ) AS COL1 ,  ROUND ( W_ORDER_D.X_ATTRIB_27 - ( SYSDATE + ( 330 / 1440 ) ))  AS COL2 FROM W_ORDER_D WHERE W_ORDER_D.ORDER_TYPE = 'Service Order' AND ROW_WID IN (11010,11011) ;OUTPUT: -ROW_WID X_ATTRIB_27,     SYSDATE,                          COL1,  COL211010 6/30/2004 10:55:20 AM 5/4/2013 3:31:30 PM -77530 -323011011 6/30/2004 10:56:04 AM 5/4/2013 3:31:30 PM -77530 -3230TERADATA QUERY:-selectROW_WID ,X_ATTRIB_27,SYSDATE,(OLAP_DEV.TimeStamp_Diff_Seconds(X_ATTRIB_27,SYSDATE) + 330 * 60)/3600 as col1,OLAP_DEV.TimeStamp_Diff_Seconds(X_ATTRIB_27,SYSDATE)/3600/24  as col2 FROM OLAP_DEV.VW_W_ORDER_D_BKP04WHERE  ROW_WID IN (11010,11011)AND ORDER_TYPE = 'Service Order' OUTPUT11,011 06/30/2004 10:56:04.000000 05/04/2013 15:32:44.014000 -77519.111150  -3230.19213111,010 06/30/2004 10:55:20.000000 05/04/2013 15:32:44.014000 -77519.123372  -3230.192640`

As we achived in teradata by using TimeStamp_Diff_Seconds UDF function which you gave earlier.

I request you to write an UDF which will be given in days as if it is in oracle and should allow more than 9999 values (TD has limitatiion that  it will not allow more than 9999)

As you said I’ll do CAST for ROUND function  ( NO problem for this)