16.20 - Using a Switch Statement for the Basic Algorithm - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

One way to write an aggregate UDF to execute the proper code required for each aggregation phase is to use the Java switch statement.

To help illustrate what is required, the discussion uses code excerpts from a simple aggregate UDF that calculates the standard deviation. For the complete code example, see UDF Code Examples.

Here is an example:

import com.teradata.fnc.*;
import java.io.*;
import java.sql.*;

...

public class UDFExample {

   public static Double stdDev(Phase phase, Context[] context, double x)
   throws SQLException
   {
      ...

      /* switch to determine the aggregation phase */
      switch (phase.getPhase()) {
         /* The AGR_INIT phase is executed once per group and       */
         /* allocates and initializes intermediate storage.         */
         case Phase.AGR_INIT:
            /* Get storage for intermediate aggregate values.       */
            ...
            /* Initialize the intermediate aggregate values.        */
            ...
            /* Fall through to the next phase because this phase    */
            /* passes in the first set of values for the group.     */
         /* The AGR_DETAIL phase is executed once for each selected */
         /* row to aggregate. One copy will run on each AMP.        */
         case Phase.AGR_DETAIL:
            ...
            break;

         /* The AGR_COMBINE phase combines the results of           */
         /* ALL individual AMPs for each group.                     */
         case Phase.AGR_COMBINE:
            ...
            break;

         /* The AGR_FINAL phase returns the final result.           */
         /* It is called once for each group.                       */
         case Phase.AGR_FINAL:
            ...

         case Phase.AGR_NODATA:
            /* Not expecting no data. */
            return -1;

         default:
            /* If it gets here there must be an error because this  */
            /* UDF does not accept any other phase options          */
            throw new SQLException("Invalid Phase", "38U05");
      }

      /* Save the intermediate results in the aggregate storage. */
      ...

      ...
   }
}