FLOAT Data Type

Description

The FLOAT data type is a subtype of NUMBER. It can be specified with or without precision, which has the same definition it has forNUMBERand can range from 1 to 126. Scale cannot be specified but is interpreted from the data. (Oracle Language Reference Float Data Type)

Notes on arithmetic operations

Please be aware that every operation performed on numerical datatypes is internally stored as a Number. Furthermore, depending on the operation performed it is possible to incur an error related to how intermediate values are stored within Snowflake, for more information please check this post on Snowflake's post on intermediate numbers in Snowflake.

Sample Source Patterns

Please, consider the following table and its inserts for the examples below:

Float data type in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE float_data_type_table(
col1 FLOAT,
col2 FLOAT(5),
col3 FLOAT(126)
);

INSERT INTO float_data_type_table (col1) VALUES (100.55555);
INSERT INTO float_data_type_table (col1) VALUES (1.9);
INSERT INTO float_data_type_table (col2) VALUES (1.23);
INSERT INTO float_data_type_table (col2) VALUES (7.89);
INSERT INTO float_data_type_table (col2) VALUES (12.79);
INSERT INTO float_data_type_table (col2) VALUES (123.45);
INSERT INTO float_data_type_table (col3) VALUES (1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111.99999999999999999999555555);

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE float_data_type_table (
col1 FLOAT,
col2 FLOAT(5),
col3 FLOAT(126)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO float_data_type_table(col1) VALUES (100.55555);

INSERT INTO float_data_type_table(col1) VALUES (1.9);

INSERT INTO float_data_type_table(col2) VALUES (1.23);

INSERT INTO float_data_type_table(col2) VALUES (7.89);

INSERT INTO float_data_type_table(col2) VALUES (12.79);

INSERT INTO float_data_type_table(col2) VALUES (123.45);

INSERT INTO float_data_type_table(col3) VALUES (1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111.99999999999999999999555555);

FLOAT

There are no differences between Oracle and Snowflake regarding FLOAT data type without precision.

Oracle

Query

IN -> Oracle_02.sql

SELECT col1 FROM float_data_type_table;

Result

col1     |
---------+
100.55555|
      1.9|

Snowflake

Query

OUT -> Oracle_02.sql

SELECT col1 FROM
float_data_type_table;

Result

col1     |
---------+
100.55555|
      1.9|

FLOAT ( p )

Queries results may not be equivalent when the precision (p) is specified in theFLOATdata type. There are small rounding differences.

Oracle

Query

IN -> Oracle_03.sql

SELECT col2 FROM float_data_type_table;

SELECT col3 FROM float_data_type_table;

Result

col2|
----+
 1.2|
 7.9|
  13|
 120|
 
 col3                                                                                               |
----------------------------------------------------------------------------------------------------+
1111111111111111111111111111111111111100000000000000000000000000000000000000000000000000000000000000|

Snowflake

Query

OUT -> Oracle_03.sql

SELECT col2 FROM
float_data_type_table;

SELECT col3 FROM
float_data_type_table;

Result

col2  |
------+
  1.23|
  7.89|
 12.79|
123.45|

col3