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 and MSCEWI3083 for more information.

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. 

CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT NUMBER)
RETURNS INT
IMMUTABLE
AS
$$
    TRUNC(INPUT)
$$;

Oracle

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

Snowflake

SELECT

TRUNC_UDF ( 1.000001 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 15.79 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -975.975 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 135.135 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/
FROM DUAL;

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.

CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT NUMBER, SCALE NUMBER)
RETURNS INT
AS
$$
    TRUNC(INPUT, SCALE)
$$;

Oracle

--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;

Snowflake

--TRUNC(NUMBER, SCALE)
SELECT

TRUNC_UDF ( 1.000001, -2 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 1.000001, -1 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 1.000001, 0 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 1.000001, 1 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 1.000001, 2 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 15.79, -2) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 15.79, -1) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 15.79, 0) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 15.79, 1 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 15.79, 50 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -9.6, -2 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -9.6, -1 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -9.6, 0 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -9.6, 1 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -9.6, 2 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -975.975, -3 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -975.975, -2 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -975.975, -1 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -975.975, 0 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -975.975, 1 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -975.975, 2 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -975.975, 3 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( -975.975, 5 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 135.135, -10 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 135.135, -2 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 135.135, 0 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 135.135, 1 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 135.135, 2 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 135.135, 3 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/,
TRUNC_UDF ( 135.135, 5 ) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/
FROM DUAL;

Known Issues

No issues were found.

Related EWIs

  1. MSCEWI3083: Unhandled arguments for the TRUNC function.

PreviousTRUNC (date) UDFNextBuilt-In packages

Last updated