Expand On Clause
Translation reference to convert Teradata Expand On functionality to Snowflake
Description
The Expand On clause expands a column having a period data type, creating a regular time series of rows based on the period value in the input row. For more information about Expand On clause, see the Teradata documentation.
Sample Source Patterns
Sample data
CREATE TABLE table1
(
id INTEGER,
pd PERIOD (TIMESTAMP)
);
INSERT INTO table1 VALUES (1, PERIOD(TIMESTAMP '2022-05-23 10:15:20.00009', TIMESTAMP '2022-05-23 10:15:25.000012'));
CREATE TABLE PUBLIC.table1
(
id INTEGER,
pd VARCHAR(58) /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/
)
INSERT INTO PUBLIC.table1
VALUES (1, PUBLIC.PERIOD_UDF(TIMESTAMP '2022-05-23 10:15:20.00009', TIMESTAMP '2022-05-23 10:15:25.000012') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/);
Expand On Clause
Suppose you want to expand the period column by seconds, for this Expand On clause has anchor period expansion and interval literal expansion.
Anchor Period Expansion
SELECT id, BEGIN(bg) FROM table1 EXPAND ON pd AS bg BY ANCHOR ANCHOR_SECOND;
Snowflake doesn't support Expand On clause. To reproduce the same results and functionality, the Teradata SQL code will be contained in a CTE block, with an EXPAND_ON_UDF and TABLE function, using FLATTEN function to return multiple rows, ROW_COUNT_UDF and DIFFTTIME_PERIOD_UDF to indicate how many rows are needed and returning VALUE to help the EXPAND_ON_UDF to calculate the different regular time series. This CTE block returns the same expand columns alias as in the Expand On clause, so the result can be used in any usage of period datatype.
WITH ExpandOnCTE AS
(
SELECT
PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, pd) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'EXPAND_ON_UDF' INSERTED. ***/ bg
FROM
PUBLIC.table1,
TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFFTTIME_PERIOD_UDF('ANCHOR_SECOND', pd) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DIFFTTIME_PERIOD_UDF' INSERTED. ***/) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'ROW_COUNT_UDF' INSERTED. ***/))
)
SELECT
id,
PUBLIC.PERIOD_BEGIN_UDF(bg) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_BEGIN_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/
FROM
PUBLIC.table1,
ExpandOnCTE
-- ** MSC-WARNING - MSCEWI2078 - THE EXPAND ON CLAUSE FUNCTIONALITY IS TRANSFORMED INTO A CTE BLOCK **
-- EXPAND ON pd AS bg BY ANCHOR ANCHOR_SECOND
;
Known Issues
The Expand On clause can use interval literal expansion, for this case, SnowConvert will add an error that this translation is planned for the future.
Interval literal expansion
SELECT id,BEGIN(bg) FROM table1 EXPAND ON pd AS bg BY INTERVAL '1' SECOND;
SELECT
id,
PUBLIC.PERIOD_BEGIN_UDF(bg) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_BEGIN_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/ FROM
table1
----** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'EXPAND ON' NODE **
--EXPAND ON pd AS bg BY INTERVAL '1' SECOND
;
Related EWIs
MSCEWI1073: Pending Functional Equivalence Review
MSCEWI2078: The Expand On clause functionality is transformed into a CTE block
Last updated
Was this helpful?