fbpx

This article shows you a possible solution to handle rounding issues in Teradata.

Let’s assume we have the following table definition:

CREATE VOLATILE TABLE ROUNDING_PROBLEM
(
            AMOUNT DECIMAl(18,2),
            MONTHS INTEGER
) NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;

INSERT INTO ROUNDING_PROBLEM VALUES (10.25,3);

Let’s further assume that our task is to divide each amount by the number of months; this means per record, you have to do the following calculation:

AMOUNT / MONTHS

This is not challenging from an implementation point of view but leads to Teradata rounding problems:

SELECT The_Month,SUM(val)
FROM
(
SELECT 1 as the_month ,AMOUNT/MONTHS AS val FROM ROUNDING_PROBLEM
UNION ALL
SELECT 2 the_month ,AMOUNT/MONTHS as val FROM ROUNDING_PROBLEM
UNION ALL
SELECT 3 the_month ,AMOUNT/MONTHS as val FROM ROUNDING_PROBLEM
) x
GROUP BY 1
ORDER BY 1
;

Unfortunately, you will end up with this result:

The_Month        Sum(val)
1                      3,42
2                      3,42
3                      3,42
10,26

 

As you can see, the result is 10,26, but the sum should be 10,25.
There is no easy solution for this as we only have two digits of the precision available in our table.

Still, there is a solution: create an additional record holding the rounding difference (in my example, I added it to the last month):

SELECT The_Month,SUM(val)
FROM
(
SELECT 1 as the_month ,AMOUNT/MONTHS AS val FROM ROUNDING_PROBLEM
UNION ALL
SELECT 2 the_month ,AMOUNT/MONTHS as val FROM ROUNDING_PROBLEM
UNION ALL
SELECT 3 the_month ,AMOUNT/MONTHS as val FROM ROUNDING_PROBLEM
UNION ALL
SELECT 3, AMOUNT – (AMOUNT/MONTHS*MONTHS) FROM ROUNDING_PROBLEM
) x
GROUP BY 1
ORDER BY 1
;

The_Month        Sum(val)
1                      3,42
2                      3,42
3                      3,41
10,25

Now we can add up the monthly amounts and have the correct results in aggregations. On the other hand, we have an additional record available that identifies the rounding differences. It could be stored with a particular identification code to distinguish it from the regular records.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>