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

INPUT: The NUMBER that needs to be truncated.

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

IN -> Oracle_01.sql

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

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

Snowflake

OUT -> Oracle_01.sql

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

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

INPUT: The NUMBER that needs to be truncated.
SCALE: Represents the number of digits the output will include after the decimal point.

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

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;

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

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;

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.

No related EWIs.

PreviousTRUNC (date) UDF NextTO_NUMBER

Last updated 6 months ago

Description

Custom UDF overloads

TRUNC_UDF(n1)

Oracle

Snowflake

TRUNC_UDF(n1, n2)

Oracle

Snowflake

Known Issues

Related EWIs