ROW_COUNT_UDF

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Definition

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

PUBLIC.ROW_COUNT_UDF(NROWS DOUBLE)

Parameters

NROWS DOUBLE

The number returned in DIFFTIME_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:

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

Output:

WITH
--** MSC-WARNING - MSCEWI2078 - THE EXPAND ON CLAUSE FUNCTIONALITY IS TRANSFORMED INTO A CTE BLOCK **
ExpandOnCTE AS
(
SELECT
PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, duration) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'EXPAND_ON_UDF' INSERTED. ***/ bg
FROM
project,
TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFF_TIME_PERIOD_UDF('ANCHOR_SECOND', duration) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DIFF_TIME_PERIOD_UDF' INSERTED. ***/) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'ROW_COUNT_UDF' INSERTED. ***/))
)
SELECT NORMALIZE emp_id,
duration
FROM
project,
ExpandOnCTE;