All Forums Database
e-smile 16 posts Joined 06/12
21 Jun 2012
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

 

dnoeth 4628 posts Joined 11/04
21 Jun 2012

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

Dieter

e-smile 16 posts Joined 06/12
22 Jun 2012

thank you very much Dieter.

 

See you later.

 

Best Regards;

Ismail Arslan

maheshkumittal 2 posts Joined 11/11
08 Oct 2012

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.
 

ulrich 816 posts Joined 09/09
08 Oct 2012

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.
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

03 May 2013

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.

 

 

 

 

Thanx,
Mahesh

dnoeth 4628 posts Joined 11/04
03 May 2013

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

Dieter

04 May 2013

 
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.
 
 

Thanx,
Mahesh

04 May 2013

 


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.
 

Thanx,
Mahesh

07 May 2013

Hi Dieter,
                    Thanks for your reply :)
 
We achieved it successfully
                

Thanx,
Mahesh

You must sign in to leave a comment.