Hi, when I execute the following SQL
, cast(net_quant*42*(mvt_gravity*8.338426855)-0.0101578 as DECIMAL(19,13))
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
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 ...
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)
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?
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.