TD_SUM_NORMALIZE_MEET - Teradata Database

TD_SUM_NORMALIZE_MEET

Purpose  

Finds the sum of a column for all the rows that were normalized because their Period values meet.

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.
numeric_column	a numeric column on which SUM() is requested. 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_SUM_NORMALIZE_MEET 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_SUM_NORMALIZE_MEET is a table function that takes three 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 a numeric column on which SUM() is requested. All numeric data types are supported. You must specify this argument as a dynamic UDT where the column is an attribute of the UDT.

The third argument is the Period column where you want to find the Period values that meet.

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 must include a numeric output column to contain the sum result value. The data type of this column should be the same data type as the corresponding input column. To prevent a possible overflow error, you can use the CAST function to convert the data type of the input column to a larger numeric data type.

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, charges, duration) AS

      (SELECT flight_id, charges, duration FROM FlightExp)

   SELECT * 

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

                                                 NEW VARIANT_TYPE(subtbl.charges),

                                                 subtbl.duration)

   RETURNS (flight_id INT, charges FLOAT,

            duration PERIOD(TIMESTAMP(6) WITH TIME ZONE))

   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, charges, duration) ORDER BY 1,3;