Precision loss during expression evaluation - data type issue?

Database
Teradata Employee

Precision loss during expression evaluation - data type issue?

Hi, when I execute the following SQL

 sel doc_id
, prd_id
, prd_cmn_name
, net_quant
, mvt_gravity
, cast(net_quant*42*(mvt_gravity*8.338426855)-0.0101578 as DECIMAL(19,13))
from monroe_prd.movements

I am getting the following error:

SELECT Failed 2614: Precision loss during expression evaluation

Relevant table DDL is below

CREATE SET TABLE PRD.MOVEMENTS
(
DOC_ID DECIMAL(12,0) TITLE 'SRA Document ID',
PRD_ID CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Product Code',
PRD_CMN_NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Product Name',
NET_QUANT DECIMAL(9,2) TITLE 'Net Quantity',
GROSS_QUANT DECIMAL(9,2) TITLE 'Gross Quantity',
MVT_GRAVITY DECIMAL(7,6) TITLE 'Gravity Spec',
MVT_API DECIMAL(10,7) TITLE 'Movement API',
CREATEDATETIME TIMESTAMP(0) TITLE 'CreateDate',
UPDATEDATETIME TIMESTAMP(0) TITLE 'UpdateDate')
UNIQUE PRIMARY INDEX ( DOC_ID ,DOC_NUM )
INDEX ( PRD_ID ,MVT_DATE );

After reviewing the Messages manual, I have tried casting the result as 'FLOAT' and many other combinations of DECIMAL(m,n) and get the same error.  I did the equation in a graphing calculator for a specific row and came up with a DECIMAL(19,13) answer, which is why i've been trying to cast as that data type.  Ultimately I'd like to round the answer to 4 decimal places, but I have not yet gotten to that point.

Thanks in advance for any ideas that you have

2 REPLIES

Re: Precision loss during expression evaluation - data type issue?

The 2614 error says that the result is too small.  I plugged some literals your  cast stmt, and got the same errors.  When i force the order of operations and limit the values to four decimal places (you said you wanted to round to four places) it works.

Not sure if you want this evaluated left to right, but ...

SELECT 123456.33*42*(1.888333*8.338426855)-0.0101578

generates a 2614 error, casting that as float generates the error as well.

SELECT CAST(123456.3333*((42.0000*(1.8883*8.998))- 0.01015) AS FLOAT)

extending the integer of 42 to 42.0000 and limiting the other values to four decimal places returns a result....

SELECT CAST(123456.3333*((42.0000*(1.8883*8.338))- 0.0101578) AS FLOAT)

interesting conundrum...

SELECT CAST((123456.3333*42*1.8883*8.3384269) - .0101578 AS FLOAT)

the last row works as well.

What does your data profile look like, for the columns involved in the calculation?

Teradata Employee

Re: Precision loss during expression evaluation - data type issue?

Thanks for your help!

The data profile is true to the data type.  MVT_Gravity is the specific gravity of a substance so I need to take that all the way to 6 decimal places.  

MAX net_quant = 642,000.00

MIN net_qant = -333,200.00

MAX MVT_gravity = 1.797967

MIN MVT_gravity = 0.506400

As this is a conversion formula, those constants (8.338426855 and 0.0101578) need to stay at that level of precision for the calculation, then round my final answer to 4 decimals.  These are petroleum inventories so there are pretty strict standards regarding decimal precision.