### FLOAT data type migration

Sybase FLOAT is generally used to save non integer numbers like fractions where no number of precision can hold the exact value. Sybase FLOATs do not store an exact value

"It stores slightly imprecise representations of real numbers as binary fractions at the hardware level"

http://www.sybase.com/detail?id=20313

Oracle has two data types, FLOAT and BINARY_FLOAT.

http://stackoverflow.com/questions/332492/oracle-floats-vs-number

If you migrate from Sybase FLOAT to Oracle BINARY_FLOAT then any value inserted will be treated as a binary imprecise number, just like Sybase.

SYBASE

drop table testfloat

go

create table testfloat (floatcol float)

go

insert into testfloat values(1.005)

go

insert into testfloat values(1.0049999999999999)

go

select * from testfloat --both values are displayed as 1.005

go

select * from testfloat where floatcol = 1.005 --returns both rows

go

select * from testfloat where floatcol = 1.0049999999999999 --returns both rows

go

select * from testfloat where floatcol = 1.0049999999999998 --returns both rows

go

select * from testfloat where floatcol = 1.0049999999999997 --returns NO rows

go

select * from testfloat where floatcol = 1.004999994--returns NO rows

go

select * from testfloat where floatcol = 1.004999493 --returns NO rows

go

So it looks like Sybase stores 1.005 as 1.0049999999999999, but displays it to the clients (like isql, jdbc) as 1.005

A comparison can be made with the literal 1.005 (though not recommended as float should never be compared in this way)

ORACLE

DROP table testfloat;

create table testfloat(floatcol float, binaryfloatcol binary_float ,varchar2floatcol varchar2(100) );

insert into testfloat values(1.005,1.005,'1.005') ;

insert into testfloat values(1.0049999999999999,1.0049999999999999,'1.0049999999999999');

select * from testfloat; --both values are displayed as 1.005 for BINARY_FLOAT and a mix as FLOAT

select * from testfloat where floatcol = 1.005; --returns one row

select * from testfloat where floatcol = 1.0049999999999999 ; --returns one row

select * from testfloat where floatcol = 1.0049999999999998 ; --returns NO rows

select * from testfloat where floatcol = 1.0049999999999997 ; --returns NO rows

select * from testfloat where binaryfloatcol = 1.005; --returns two rows

select * from testfloat where binaryfloatcol = 1.0049999999999999 ; --returns two rows

select * from testfloat where binaryfloatcol = 1.0049999999999998 ; --returns two rows

select * from testfloat where binaryfloatcol = 1.0049999999999997 ; --returns two rows

select * from testfloat where binaryfloatcol = 1.004999994 ; --returns two rows

select * from testfloat where binaryfloatcol = 1.004999493 ; --returns no row

Oracle stores FLOAT values as explicit numbers whereas BINARY_FLOAT values are not as precise. Oracle also displays 1.005 for both values in BINARY_FLOAT.

Conclusion

From the above test cases it looks like Oracle BINARY_FLOAT is a much better match than Oracle FLOAT for Sybase FLOAT, but it should be noted that since Sybase FLOAT and Oracle BINARY_FLOAT are not precise numbers , comparing equivalence is not identical between databases.

floats , be they Sybase FLOATs or Oracle BINARY_FLOATs should never be directly compared, rather a range of values should be matched.

Notes

Sybase BCP dumps out both floats as 1.0049999999999999

JDBC query against Sybase returns both floats as 1.005.

So during a SQL Developer data move, online will insert 1.005 , but offline will insert 1.0049999999999999. This doesn't really matter if the Oracle data type is BINARY_FLOAT, but it does if you use Oracle FLOAT data type.

DB2 behaves the exact same way as Sybase. So DB2 FLOAT should also be converted to Oracle BINARY_FLOAT.

"It stores slightly imprecise representations of real numbers as binary fractions at the hardware level"

http://www.sybase.com/detail?id=20313

Oracle has two data types, FLOAT and BINARY_FLOAT.

http://stackoverflow.com/questions/332492/oracle-floats-vs-number

- FLOAT is really a decimal data type with exact values (basically it is a NUMERIC)
- BINARY_FLOAT is a binary data type which better maps to Sybase FLOAT data type

If you migrate from Sybase FLOAT to Oracle BINARY_FLOAT then any value inserted will be treated as a binary imprecise number, just like Sybase.

SYBASE

drop table testfloat

go

create table testfloat (floatcol float)

go

insert into testfloat values(1.005)

go

insert into testfloat values(1.0049999999999999)

go

select * from testfloat --both values are displayed as 1.005

go

select * from testfloat where floatcol = 1.005 --returns both rows

go

select * from testfloat where floatcol = 1.0049999999999999 --returns both rows

go

select * from testfloat where floatcol = 1.0049999999999998 --returns both rows

go

select * from testfloat where floatcol = 1.0049999999999997 --returns NO rows

go

select * from testfloat where floatcol = 1.004999994--returns NO rows

go

select * from testfloat where floatcol = 1.004999493 --returns NO rows

go

So it looks like Sybase stores 1.005 as 1.0049999999999999, but displays it to the clients (like isql, jdbc) as 1.005

A comparison can be made with the literal 1.005 (though not recommended as float should never be compared in this way)

ORACLE

DROP table testfloat;

create table testfloat(floatcol float, binaryfloatcol binary_float ,varchar2floatcol varchar2(100) );

insert into testfloat values(1.005,1.005,'1.005') ;

insert into testfloat values(1.0049999999999999,1.0049999999999999,'1.0049999999999999');

select * from testfloat; --both values are displayed as 1.005 for BINARY_FLOAT and a mix as FLOAT

select * from testfloat where floatcol = 1.005; --returns one row

select * from testfloat where floatcol = 1.0049999999999999 ; --returns one row

select * from testfloat where floatcol = 1.0049999999999998 ; --returns NO rows

select * from testfloat where floatcol = 1.0049999999999997 ; --returns NO rows

select * from testfloat where binaryfloatcol = 1.005; --returns two rows

select * from testfloat where binaryfloatcol = 1.0049999999999999 ; --returns two rows

select * from testfloat where binaryfloatcol = 1.0049999999999998 ; --returns two rows

select * from testfloat where binaryfloatcol = 1.0049999999999997 ; --returns two rows

select * from testfloat where binaryfloatcol = 1.004999994 ; --returns two rows

select * from testfloat where binaryfloatcol = 1.004999493 ; --returns no row

Oracle stores FLOAT values as explicit numbers whereas BINARY_FLOAT values are not as precise. Oracle also displays 1.005 for both values in BINARY_FLOAT.

Conclusion

From the above test cases it looks like Oracle BINARY_FLOAT is a much better match than Oracle FLOAT for Sybase FLOAT, but it should be noted that since Sybase FLOAT and Oracle BINARY_FLOAT are not precise numbers , comparing equivalence is not identical between databases.

floats , be they Sybase FLOATs or Oracle BINARY_FLOATs should never be directly compared, rather a range of values should be matched.

Notes

Sybase BCP dumps out both floats as 1.0049999999999999

JDBC query against Sybase returns both floats as 1.005.

So during a SQL Developer data move, online will insert 1.005 , but offline will insert 1.0049999999999999. This doesn't really matter if the Oracle data type is BINARY_FLOAT, but it does if you use Oracle FLOAT data type.

**Update For DB2**DB2 behaves the exact same way as Sybase. So DB2 FLOAT should also be converted to Oracle BINARY_FLOAT.