search

TRUNC (number) UDF

hashtag
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 Referencearrow-up-right)

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)arrow-up-right section and MSCEWI3083arrow-up-right for more information.

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

hashtag
Custom UDF overloads

hashtag
TRUNC_UDF(n1)

It calls Snowflake TRUNC functionarrow-up-right 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)
$$;

hashtag
Oracle

hashtag
Snowflake

hashtag
TRUNC_UDF(n1, n2)

It calls Snowflake TRUNC functionarrow-up-right 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.

hashtag
Oracle

hashtag
Snowflake

hashtag
Known Issues

No issues were found.

hashtag
Related EWIs

  1. MSCEWI3083arrow-up-right: Unhandled arguments for the TRUNC function.

PreviousTRUNC (date) UDFNextBuilt-In packages

Last updated

Was this helpful?