Using a Switch Statement for the Basic Algorithm | Teradata Vantage - 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 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;
}