ROW_COUNT_UDF

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.

Definition

Returns an array from 1 to the number returned in DIFF_TIME_PERIOD_UDF.

PUBLIC.ROW_COUNT_UDF(NROWS DOUBLE)

Parameters

NROWS DOUBLE

The number returned in DIFF_TIME_PERIOD_UDF.

Returns

An array to calculate how many rows are needed to simulate the expand-on clause.

Usage example

Input:

SELECT ROW_COUNT_UDF(DIFFTTIME_PERIOD('SECONDS','2022-11-26 10:15:20.000*2022-11-26 10:15:25.000'));

Output:

[1, 2, 3, 4, 5]

Migration example

Input:

IN -> Teradata_01.sql

SELECT NORMALIZE emp_id, duration FROM project EXPAND ON duration AS bg BY ANCHOR ANCHOR_SECOND;

Output:

OUT -> Teradata_01.sql

WITH ExpandOnCTE AS
(
SELECT
    PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, duration) bg
FROM
    project,
TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFF_TIME_PERIOD_UDF('ANCHOR_SECOND', duration))))
)
SELECT NORMALIZE emp_id,
    duration
FROM
    project,
    ExpandOnCTE;