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

Teradata Vantage™ - SQL External Routine Programming

Advanced SQL Engine
Teradata Database
Release Number
Release Date
April 2020
Content Type
Programming Reference
Publication ID
English (United States)

One way to write an aggregate UDF to execute the proper code required for an aggregation phase is to use the C 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 C Aggregate Function.

Here is an example:

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
#include <math.h>
void STD_DEV ( FNC_Phase     phase,
               FNC_Context_t *fctx,
               FLOAT         *x,
               FLOAT         *result,
               int           *x_i,
               int           *result_i,
               char          sqlstate[6],
               SQL_TEXT      fncname[129],
               SQL_TEXT      sfncname[129],
               SQL_TEXT      error_message[257])
   /* switch to determine the aggregation phase */
   switch (phase)
      /* This phase is executed once per group and */
      /* allocates and initializes intermediate    */
      /* aggregate storage */
      case AGR_INIT:
         /* Get storage for intermediate aggregate values. */
         /* Initialize the intermediate aggregate values   */
      /* Fall through to detail phase, because the      */
      /* AGR_INIT phase passes in the first set of      */
      /* values for the group.                          */
      /* This phase is executed once for each selected    */
      /* row to aggregate. One copy will run on each AMP. */
      case AGR_DETAIL:
      /* This phase combines the results of ALL           */
      /* individual AMPs for each group.                  */
      case AGR_COMBINE:
      /* This phase returns the final result. */
      /* It is called once for each group.    */
      case AGR_FINAL:
      case AGR_NODATA:
         /* return null if no data */
         *result_i = -1;
         /* If it gets here there must be an error because this */
         /* function does not accept any other phase options */
         strcpy(sqlstate, "U0005");