ROW_COUNT_UDF

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;