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

PreviousSelect StatementNextNormalize

Last updated