TRUNC (number) UDF

Description

The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point. (Oracle TRUNC(number) SQL Language Reference)

TRUNC(n1 [, n2 ])

TRUNC_UDF for numeric values will be added to handle cases where the first column has an unrecognized data type.

Example:

SELECT TRUNC(column1) FROM DUAL;

If the definition of column1 was not provided to the tool. Then the TRUNC_UDF will be added and in execution time, the overload of TRUNC_UDF will handle the case if it is a numeric or a date type.

Please refer to TRUNC (DATE) section.

The following sections provide the proof that TRUNC_UDF will handle perfectly numeric values.

Custom UDF overloads

TRUNC_UDF(n1)

It calls Snowflake TRUNC function with the input number. This overload exists in order to handle the different types of parameter scenarios, in case that information is not available during the migration.

Parameters

1. INPUT: The NUMBER that needs to be truncated.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT NUMBER)
RETURNS INT
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    TRUNC(INPUT)
$$;

Oracle

Query

IN -> Oracle_01.sql

--TRUNC(NUMBER)
SELECT
	TRUNC ( 1.000001 ),
	TRUNC ( 15.79 ),
	TRUNC ( -975.975 ),
	TRUNC ( 135.135 )
FROM DUAL;

Result

TRUNC(1.000001)|TRUNC(15.79)|TRUNC(-975.975)|TRUNC(135.135)|
---------------+------------+---------------+--------------+
              1|          15|           -975|           135|

Snowflake

Query

OUT -> Oracle_01.sql

--TRUNC(NUMBER)
SELECT
	TRUNC ( 1.000001 ),
	TRUNC ( 15.79 ),
	TRUNC ( -975.975 ),
	TRUNC ( 135.135 )
FROM DUAL;

Result

TRUNC_UDF(1.000001)|TRUNC_UDF(15.79)|TRUNC_UDF(-975.975)|TRUNC_UDF(135.135)|
-------------------+----------------+-------------------+------------------+
                  1|              15|               -975|               135|

TRUNC_UDF(n1, n2)

It calls Snowflake TRUNC function with the input number and the scale. This overload exists in order to handle the different types of parameter scenarios, in case that information is not available during the migration.

Parameters

1. INPUT: The NUMBER that needs to be truncated.

2. SCALE: Represents the number of digits the output will include after the decimal point.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT NUMBER, SCALE NUMBER)
RETURNS INT
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    TRUNC(INPUT, SCALE)
$$;

Oracle

Query

IN -> Oracle_02.sql

--TRUNC(NUMBER, SCALE)
SELECT
	TRUNC ( 1.000001, -2 ),
	TRUNC ( 1.000001, -1 ),
	TRUNC ( 1.000001, 0 ),
	TRUNC ( 1.000001, 1 ),
	TRUNC ( 1.000001, 2 ),
	TRUNC ( 15.79, -2),
	TRUNC ( 15.79, -1),
	TRUNC ( 15.79, 0),
	TRUNC ( 15.79, 1 ),
	TRUNC ( 15.79, 50 ),
	TRUNC ( -9.6, -2 ),
	TRUNC ( -9.6, -1 ),
	TRUNC ( -9.6, 0 ),
	TRUNC ( -9.6, 1 ),
	TRUNC ( -9.6, 2 ),
	TRUNC ( -975.975, -3 ),
	TRUNC ( -975.975, -2 ),
	TRUNC ( -975.975, -1 ),
	TRUNC ( -975.975, 0 ),
	TRUNC ( -975.975, 1 ),
	TRUNC ( -975.975, 2 ),
	TRUNC ( -975.975, 3 ),
	TRUNC ( -975.975, 5 ),
	TRUNC ( 135.135, -10 ),
	TRUNC ( 135.135, -2 ),
	TRUNC ( 135.135, 0 ),
	TRUNC ( 135.135, 1 ),
	TRUNC ( 135.135, 2 ),
	TRUNC ( 135.135, 3 ),
	TRUNC ( 135.135, 5 )
FROM DUAL;

Result

TRUNC(1.000001,-2)|TRUNC(1.000001,-1)|TRUNC(1.000001,0)|TRUNC(1.000001,1)|TRUNC(1.000001,2)|TRUNC(15.79,-2)|TRUNC(15.79,-1)|TRUNC(15.79,0)|TRUNC(15.79,1)|TRUNC(15.79,50)|TRUNC(-9.6,-2)|TRUNC(-9.6,-1)|TRUNC(-9.6,0)|TRUNC(-9.6,1)|TRUNC(-9.6,2)|TRUNC(-975.975,-3)|TRUNC(-975.975,-2)|TRUNC(-975.975,-1)|TRUNC(-975.975,0)|TRUNC(-975.975,1)|TRUNC(-975.975,2)|TRUNC(-975.975,3)|TRUNC(-975.975,5)|TRUNC(135.135,-10)|TRUNC(135.135,-2)|TRUNC(135.135,0)|TRUNC(135.135,1)|TRUNC(135.135,2)|TRUNC(135.135,3)|TRUNC(135.135,5)|
------------------+------------------+-----------------+-----------------+-----------------+---------------+---------------+--------------+--------------+---------------+--------------+--------------+-------------+-------------+-------------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+------------------+-----------------+----------------+----------------+----------------+----------------+----------------+
                 0|                 0|                1|                1|                1|              0|             10|            15|          15.7|          15.79|             0|             0|           -9|         -9.6|         -9.6|                 0|              -900|              -970|             -975|           -975.9|          -975.97|         -975.975|         -975.975|                 0|              100|             135|           135.1|          135.13|         135.135|         135.135|

Snowflake

Query

OUT -> Oracle_02.sql

--TRUNC(NUMBER, SCALE)
SELECT
	TRUNC ( 1.000001, -2 ),
	TRUNC ( 1.000001, -1 ),
	TRUNC ( 1.000001, 0 ),
	TRUNC ( 1.000001, 1 ),
	TRUNC ( 1.000001, 2 ),
	TRUNC ( 15.79, -2),
	TRUNC ( 15.79, -1),
	TRUNC ( 15.79, 0),
	TRUNC ( 15.79, 1 ),
	TRUNC ( 15.79, 50 ),
	TRUNC ( -9.6, -2 ),
	TRUNC ( -9.6, -1 ),
	TRUNC ( -9.6, 0 ),
	TRUNC ( -9.6, 1 ),
	TRUNC ( -9.6, 2 ),
	TRUNC ( -975.975, -3 ),
	TRUNC ( -975.975, -2 ),
	TRUNC ( -975.975, -1 ),
	TRUNC ( -975.975, 0 ),
	TRUNC ( -975.975, 1 ),
	TRUNC ( -975.975, 2 ),
	TRUNC ( -975.975, 3 ),
	TRUNC ( -975.975, 5 ),
	TRUNC ( 135.135, -10 ),
	TRUNC ( 135.135, -2 ),
	TRUNC ( 135.135, 0 ),
	TRUNC ( 135.135, 1 ),
	TRUNC ( 135.135, 2 ),
	TRUNC ( 135.135, 3 ),
	TRUNC ( 135.135, 5 )
FROM DUAL;

Result

TRUNC_UDF ( 1.000001, -2 )|TRUNC_UDF ( 1.000001, -1 )|TRUNC_UDF ( 1.000001, 0 )|TRUNC_UDF ( 1.000001, 1 )|TRUNC_UDF ( 1.000001, 2 )|TRUNC_UDF ( 15.79, -2)|TRUNC_UDF ( 15.79, -1)|TRUNC_UDF ( 15.79, 0)|TRUNC_UDF ( 15.79, 1 )|TRUNC_UDF ( 15.79, 50 )|TRUNC_UDF ( -9.6, -2 )|TRUNC_UDF ( -9.6, -1 )|TRUNC_UDF ( -9.6, 0 )|TRUNC_UDF ( -9.6, 1 )|TRUNC_UDF ( -9.6, 2 )|TRUNC_UDF ( -975.975, -3 )|TRUNC_UDF ( -975.975, -2 )|TRUNC_UDF ( -975.975, -1 )|TRUNC_UDF ( -975.975, 0 )|TRUNC_UDF ( -975.975, 1 )|TRUNC_UDF ( -975.975, 2 )|TRUNC_UDF ( -975.975, 3 )|TRUNC_UDF ( -975.975, 5 )|TRUNC_UDF ( 135.135, -10 )|TRUNC_UDF ( 135.135, -2 )|TRUNC_UDF ( 135.135, 0 )|TRUNC_UDF ( 135.135, 1 )|TRUNC_UDF ( 135.135, 2 )|TRUNC_UDF ( 135.135, 3 )|TRUNC_UDF ( 135.135, 5 )|
--------------------------+--------------------------+-------------------------+-------------------------+-------------------------+----------------------+----------------------+---------------------+----------------------+-----------------------+----------------------+----------------------+---------------------+---------------------+---------------------+--------------------------+--------------------------+--------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+-------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+
                         0|                         0|                        1|                      1.0|                     1.00|                     0|                    10|                   15|                  15.7|                  15.79|                     0|                     0|                   -9|                 -9.6|                 -9.6|                         0|                      -900|                      -970|                     -975|                   -975.9|                  -975.97|                 -975.975|                 -975.975|                         0|                      100|                     135|                   135.1|                  135.13|                 135.135|                 135.135|s

Known Issues

No issues were found.

Related EWIs

No related EWIs.