cast used with to_number operator giving unexpected output

Suhrid_R__Ghosh

Hello,

 

We are using vertica version : 7.2.3-2

 

Following unexpected thing i am facing :

 

I am getting a varchar column which i am converting to integer and doing cast on top of that.

 

Below is the query which i tried:

 

tdaadmin=> select cast(to_number('0.325000') as decimal(19,2));
to_number
-----------
0.33

 

This is the result which i expected and it came perfect , rounding off the value.

 

Now comes the below case :

 

tdaadmin=> select cast(to_number('0.725000') as decimal(19,2));
to_number
-----------
0.72
(1 row)

 

tdaadmin=> select cast(to_number('1.525') as decimal(19,2));
to_number
-----------
1.52
(1 row)

 

STRANGE ... It is not rounding off for the above , its giving unexpected o/p

 

Please can any one help me on the same.

 

 

Thanks,
Suhrid Ghosh

 

 

 

kaurora

It's the to_number that messing things up. have you tried using the column name as is? without the to_number conversion?