When i an casting the below to an interger its giving me incorrect result. Is it a Bug in Teradata?
SELECT cast('701479229.000000000000000' AS INTEGER) --701479228
I think it is something realted to the range integer can handle. as if you remove one of the 0, the results appears to be fine.
You can use decimal to avoid this error.
SELECT cast('701479229.000000000000000' AS DECIMAL(9,0));
Not just remove one of the zeros, even if you add one or more zeros, it works fine. Not sure whats the exact reason here but its better to go with decimal approach as Khurram suggested.
works fine for me, i'ts probably a client problem.
Which client and which connection is used? ODBC?
I feel the best test is to do in Unix or Linux environment. Please let me know your views.
I have checked both in BTEQ, and SQLA, but it is giveing the same result. Can you please share your ODBC settings?
I tried as follows:
sel cast(965874.00000000000000000 as Decimal(18,0));
--You can put any number inplace of 18.
It worked fine for me.
I am Using Teradata 13.10.0711 13.10.07.11 with both ODBC and teradata .Net connections.
I guess it has nothing to do with integer range as any number beyond this are working fine.
Only this number is not working. If you decrease or increase the number of zeros then its working.
But my question is why this particular number not working?
Well I have performed a lot of analysis at your problem. Following are some of my findings:
So I would advise you to avoid enclosing the numeric values in qoutes and stay happy!
SELECT CAST(701479229.000000000000000 AS INTEGER);