TD_NORMALIZE_OVERLAP - Teradata Database

TD_NORMALIZE_OVERLAP

Purpose  

Combines the rows whose Period values overlap so that the resulting normalized row contains the earliest beginning bound and the latest ending bound from the Period values of all the rows involved.

Syntax  

where:

Syntax element...	Specifies...
grouping_column_list	one or more grouping columns, not including the Period column. You must specify the input as a dynamic UDT.
period_column	a column with a data type of PERIOD(DATE), PERIOD(TIMESTAMP), or PERIOD(TIMESTAMP WITH TIME ZONE).

Invocation

TD_NORMALIZE_OVERLAP is an embedded services system function. For information on activating and invoking embedded services functions, see “Embedded Services System Functions” on page 24.

Usage Notes

TD_NORMALIZE_OVERLAP is a table function that takes two arguments. The arguments passed to the function are the specified columns in a subtable derived from using the WITH Request Modifier as follows:

The first argument is one or more grouping columns, not including the Period column. You must specify this argument as a dynamic UDT, where each column is an attribute of the UDT. For more information, see “NEW VARIANT_TYPE” on page 1350.

The second argument is the Period column where you want to find the Period values that overlap.

Input to the table function must be columns that are hash-redistributed on the grouping columns and sorted by the grouping columns and the Period values as follows:

You must specify a LOCAL ORDER BY clause that includes all of the grouping columns and the Period column in the same order that was specified in the input arguments. The sort order must be ascending.

You must include a HASH BY clause with at least one of the grouping columns. The HASH BY clause cannot include the Period column or any columns that are not part of the grouping columns.

You must invoke the function with a RETURNS clause that specifies the output columns as follows:

You must specify the output columns to be the same as the columns specified in the input arguments, including the Period column.

You must specify the output columns with the same data types and in the same order as the corresponding input columns.

You can specify an optional INTEGER output column at the end of the RETURNS clause to contain a count of the rows that were normalized.

Result

This function returns result rows with the columns specified in the RETURNS clause as follows:

The grouping columns specified in the input argument.

The Period column with normalized Period values.

An optional INTEGER column containing the count of the rows that were normalized because their Period values meet.

Example  

   WITH subtbl(flight_id, duration) AS

      (SELECT flight_id, duration FROM FlightExp)

   SELECT * 

   FROM TABLE (TD_SYSFNLIB.TD_NORMALIZE_OVERLAP(NEW VARIANT_TYPE(subtbl.flight_id),

                                                subtbl.duration)

   RETURNS (flight_id INT, duration PERIOD(TIMESTAMP(6) WITH TIME ZONE), NrmCount INT)

   HASH BY flight_id     /* input data is redistributed on column, flight_id */

   LOCAL ORDER BY flight_id, duration)     /* input data is sorted on these columns */

   AS DT(flight_id, duration, NrmCount) ORDER BY 1,2;