All Forums Database
Rakesh71 15 posts Joined 09/13
08 Oct 2013
Casting to An integer giving incorrect result.

Hi All,
 
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
 
Thanks,
Rakesh

M.Saeed Khurram 544 posts Joined 09/12
08 Oct 2013

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));

 

Khurram

gkatiyar 19 posts Joined 09/13
08 Oct 2013

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.

Regards,
Gaurav Katiyar

dnoeth 4628 posts Joined 11/04
08 Oct 2013

Hi Rakesh,
works fine for me, i'ts probably a client problem.
Which client and which connection is used? ODBC?
 
Dieter

Dieter

Raja_KT 1246 posts Joined 07/09
09 Oct 2013

Hi Dieter,
I feel the best test is to do in Unix or Linux environment. Please let me know your views.
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

09 Oct 2013

Hi,
 
I have tried in SQL Assistant and its worked fine for me.
 
Thanks,
Nagesh

M.Saeed Khurram 544 posts Joined 09/12
09 Oct 2013

Diether,
I have checked both in BTEQ, and SQLA, but it is giveing the same result. Can you please share your ODBC settings?
 

Khurram

mayanktiwari 6 posts Joined 10/12
09 Oct 2013

Hi Rakesh,
 
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.

Rakesh71 15 posts Joined 09/13
09 Oct 2013

Hi Dieter,
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?
 
Thnaks,
Rakesh

M.Saeed Khurram 544 posts Joined 09/12
09 Oct 2013

Well I have performed a lot of analysis at your problem. Following are some of my findings:

  1. The first thing is why are you enclosing a decimal value in qoutes? If you use the value without qoutes then it works fine with the same number of 0. qoutes should be avoided with numeric values. 
  2. The second thing is in a decimal value you can specify upto max 38 digits, But in this case the type becomes DECIMAL(24,15), I am not sure how Teradata is dealing with this value.
  3. Third thing is if you increease the number of 0, then the same roud down occuers again with 21 0s.

So I would advise you to avoid enclosing the numeric values in qoutes and stay happy!

SELECT CAST(701479229.000000000000000 AS INTEGER);

 

Khurram

Rakesh71 15 posts Joined 09/13
10 Oct 2013

We know the solution to this problem. There are many. But my question is why only for this number (in quotes) it is giving incorrect results. Why not for any other value.Definitely there is some issue in teradata in the way its hadling this particular value.

dnoeth 4628 posts Joined 11/04
10 Oct 2013

Hi Rakesh,
you should open an incident.

Dieter
 
 

Dieter

ToddAWalter 316 posts Joined 10/11
11 Oct 2013

This has been addressed in 14.10 and works properly there.
Prior - we take the string and put it into floating point. Floating point is an imprecise representation when there is a very long mantissa. We take all of the digits in the string and make them the mantissa so as that gets long, there can be a loss of precision. Then when the cast is done, it is rounded into the target type and loses precision.
We made several changes to this processing as a side effect of building the NUMBER data type.

Rakesh71 15 posts Joined 09/13
11 Oct 2013

Thank you All for the replies.

You must sign in to leave a comment.