Using a Switch Statement for the Basic Algorithm - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

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. */
      ...

      ...
   }
}