Datometry Documentation

The division rules for Azure Synapse differ from Teradata when calculating the last decimal digit. This issue becomes apparent when using the Teradata utilities RoundHalfwayMagUp = TRUE and RoundNumberAsDec = FALSE. You can specify how Hyper-Q performs rounding when using these utilities. By enabling the hint round_halfway_mag_up, Hyper-Q will return the same division values in the last decimal digit as Teradata.

To learn how Teradata performs rounding, see Rounding in the Teradata documentation.

As an example of how Hyper-Q rounds decimal numbers, consider the following table populated with the values shown.

CREATE TABLE table_name (column_a DECIMAL (28,10));
INSERT INTO table_name VALUES ('5.3009511850');
INSERT INTO table_name VALUES ('5.3009511830');
INSERT INTO table_name VALUES ('5.3009511840');
INSERT INTO table_name VALUES ('5.3009511650');
INSERT INTO table_name VALUES ('5.3009511750');
INSERT INTO table_name VALUES ('5.3009511550');
INSERT INTO table_name VALUES ('5.3009511950');

The example below illustrates how Teradata performs rounding on the values from the above table when the values are divided by 100 (column_a/100).

SELECT column_a, column_a/100 FROM table_name;

*** Query completed. 7 rows found. 2 columns returned. 
*** Total elapsed time was 2 seconds.

                      column_a                         (a/100)
------------------------------  ------------------------------
                  5.3009511830                     .0530095118
                  5.3009511840                     .0530095118
                  5.3009511950                     .0530095120
                  5.3009511550                     .0530095116
                  5.3009511650                     .0530095117
                  5.3009511750                     .0530095118
                  5.3009511850                     .0530095119

With round_halfway_mag_up=true enabled, Hyper-Q returns the following values from the above table when the values are divided by 100 (column_a/100).

SELECT
-- @dtm_round_halfway_mag_up=true
column_a, column_a/100 FROM table_name;

*** Query completed. 7 rows found. 2 columns returned. 
*** Total elapsed time was 2 seconds.

                      column_a                  (column_a/100)
------------------------------  ------------------------------
                  5.3009511830                     .0530095118
                  5.3009511840                     .0530095118
                  5.3009511650                     .0530095117
                  5.3009511850                     .0530095119
                  5.3009511750                     .0530095118
                  5.3009511550                     .0530095116
                  5.3009511950                     .0530095120

With round_halfway_mag_up=false  disabled, Hyper-Q returns the following values from the above table. Note the values labeled as incorrect due to the rounding behavior being disabled. As a point of comparison, compare the values presented below to those in the above example in which the rounding behavior is enabled.

SELECT
-- @dtm_round_halfway_mag_up=true
column_a, column_a/100 FROM table_name;
 
*** Query completed. 7 rows found. 2 columns returned. 
 *** Total elapsed time was 2 seconds.

                      column_a                  (column_a/100)                             a                        SQL_COL1
------------------------------  ------------------------------
                  5.3009511830                     .0530095118
                  5.3009511840                     .0530095118
                  5.3009511650                     .0530095117
                  5.3009511850                     .0530095118 -- incorrect 
                  5.3009511750                     .0530095117 -- incorrect 
                  5.3009511550                     .0530095115 -- incorrect 
                  5.3009511950                     .0530095119 -- incorrect 

The syntax for round_halfway_mag_up is:

round_halfway_mag_up = true | false

where:

Hint Scope

The round_halfway_mag_up hint can be specified at the statement, request, session, and global level. To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

SELECT
-- @dtm_round_halfway_mag_up=true
column_a, column_a/100 FROM table_name;

dtm set round_halfway_mag_up on;
SELECT column_a, column_a/100 FROM table_name;