SSC-FDM-OR0010

NUMBER datatype smaller precision was increased to match scale.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

The NUMBER data type stores fixed and floating-point numbers. This data is portable among different operating systems running the Oracle Database. The NUMBER data type is recommended for most cases in which you must store numeric data. The syntax is the following NUMBER (X, Y), where X is the precision and Y is the scale.

For example, NUMBER(5, 3) is a number that has 2 digits before the decimal and 3 digits after the decimal, just like the following:

12.345

Another important considerations:

1. Scale Y specifies the maximum number of digits to the right of the decimal point.

2. Scale-Precision Y-X specifies the minimum number of zeros present after the decimal point.

This message is shown when a NUMBER has a smaller precision than its scale. Snowflake does not support this feature, and this message is used to indicate that the precision's value was increased to maintain equivalence.

Please consider that there are cases where this issue can either stack alongside other known transformations or not happen at all. For example, cases where the scale is replaced by nineteen and the former precision is greater than nineteen; will NOT show this message.

Example Code

Input Code:

Queries

IN -> Oracle_01.sql

CREATE TABLE SampleNumberTable(Col1 NUMBER(4, 5));

INSERT INTO SampleNumberTable (Col1)
VALUES (0.00009);

INSERT INTO SampleNumberTable (Col1)
VALUES (0.000021);

INSERT INTO SampleNumberTable (Col1)
VALUES (0.012678912);

SELECT * FROM SampleNumberTable;

Result

Col1   |
-------+
0.00009|
0.00002|
0.01268|

Output Code:

Queries

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE SampleNumberTable (Col1 NUMBER(5, 5) /*** SSC-FDM-OR0010 - NUMBER DATATYPE SMALLER PRECISION WAS INCREASED TO MATCH SCALE ***/ /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO SampleNumberTable(Col1)
VALUES (0.00009);

INSERT INTO SampleNumberTable(Col1)
VALUES (0.000021);

INSERT INTO SampleNumberTable(Col1)
VALUES (0.012678912);

SELECT * FROM
SampleNumberTable;

Result

Col1   |
-------+
0.00009|
0.00002|
0.01268|

Recommendations

• If you need more support, you can email us at [email protected]