Using a Switch Statement for the Basic Algorithm | Teradata Vantage - 17.10 - 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
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
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:
         ...
         break;
      /* This phase combines the results of ALL           */
      /* individual AMPs for each group.                  */
      case AGR_COMBINE:
         ...
         break;
      /* This phase returns the final result. */
      /* It is called once for each group.    */
      case AGR_FINAL:
      {
         ...
         break;
      }
      case AGR_NODATA:
         /* return null if no data */
         *result_i = -1;
         break;
      default:
         /* If it gets here there must be an error because this */
         /* function does not accept any other phase options */
         strcpy(sqlstate, "U0005");
   }
   return;
}