How to Use Round Function In Teradata

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 ))

Thanks in advance.

Best Regards;

Ismail Arslan

9 REPLIES
Senior Apprentice

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.

Please Explain.

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.

FALSE traditional for Teradata Database:

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.


Senior Apprentice

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, COL2
11010 6/30/2004 10:55:20 AM 5/4/2013 3:31:30 PM -77530 -3230
11011 6/30/2004 10:56:04 AM 5/4/2013 3:31:30 PM -77530 -3230

TERADATA QUERY:-

select
ROW_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_BKP04
WHERE ROW_WID IN (11010,11011)
AND ORDER_TYPE = 'Service Order'
OUTPUT
11,011 06/30/2004 10:56:04.000000 05/04/2013 15:32:44.014000 -77519.111150 -3230.192131
11,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)

Please help me out from this plight.

Enthusiast

Re: How to Use Round Function In Teradata

Hi Dieter,

                    Thanks for your reply :)

We achieved it successfully