Casting to An integer giving incorrect result.

Database
Enthusiast

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

Tags (1)
13 REPLIES
Enthusiast

Re: Casting to An integer giving incorrect result.

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
Enthusiast

Re: Casting to An integer giving incorrect result.

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.

Senior Apprentice

Re: Casting to An integer giving incorrect result.

Hi Rakesh,

works fine for me, i'ts probably a client problem.

Which client and which connection is used? ODBC?

Dieter

Enthusiast

Re: Casting to An integer giving incorrect result.

Hi Dieter,

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

Cheers,

Raja

Enthusiast

Re: Casting to An integer giving incorrect result.

Hi,

I have tried in SQL Assistant and its worked fine for me.

Thanks,

Nagesh

Enthusiast

Re: Casting to An integer giving incorrect result.

Diether,

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

Khurram
Enthusiast

Re: Casting to An integer giving incorrect result.

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.

Enthusiast

Re: Casting to An integer giving incorrect result.

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

Enthusiast

Re: Casting to An integer giving incorrect result.

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