I have a long SQL query that ultimately generates this:
"22003[-2614] [Teradata][ODBC Teradata Driver][Teradata Database] Precision loss during expression evaluation. "
This is with the Teradata ODBC native driver - 15.00. The Teradata DB info is:
LANGUAGE SUPPORT MODE Standard
What could cause this error? Any help would be appreciated.
Much can be learned from using the TYPE function.
SELECT TYPE(-0.331872554279178), TYPE(T0.C35), TYPE(-0.331872554279178 * T0.C35), TYPE(-3.31872554279178e-01), TYPE(T0.C35), TYPE(-3.31872554279178e-01 * T0.C35));
I believe that it will show that putting the exponential notation forces the constant and the expression to FLOAT, while the decimal notation results in a decimal calculation. The type of C35 is not stated in the note above but the types of the two areguments are used to determine the result type which in the first case may not be sufficient to hold the result of the calculation. Of course that can be data dependent too in the case of decimal if insufficient decimal precision is assumed from the two types while the FLOAT will deal with most any result. On the second machine, the data could be different. Or the default precision for DECIMAL on the system could be different.
Thanks so much for this - it is very useful.
How would you determine the default precision for DECIMAL on Windows or Linux? Is it depending on the locale or regional settings, or is it a Teradata configuration setting?
I am looking at this page : http://developer.teradata.com/tools/articles/how-many-digits-in-a-decimal
which is very informative and suggests that maybe the MaxDecimal value is not the same on each machine.
I can't figure out how to get that value - I am in dbscontrol command line , but I cannot find where it is explained how to get or set that value. Maybe this is not relevant to what you said - but if it is, I'd appreciate if you could help me there.
DBSControl is documented in Chapter 9 of the Utilities Manual.
"DISPLAY GENERAL" will show all the settings in the General group which contains MaxDecimal.
Note the MaxDecimal setting is a default, not a constraint. CAST can be used to control the data type of an expression.
Eg CAST(-0.331872554279178 AS DECIMAL (38,15))