Create a User-Defined Function Example | Teradata Vantage - Example: Creating a User-defined Function To Verify a Date - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantageâ„¢

The following is an example of creating an external C language UDF.

SQL Definition

The following code in my_yyyymmdd_to_date2.sql creates the UDF.

---- UDF Build DDL/SQL--
REPLACE FUNCTION my_yyyymmdd_to_date2
(
	InputDate     VARCHAR(8) CHARACTER SET LATIN
)
RETURNS DATE
LANGUAGE C
SPECIFIC my_yyyymmdd_to_date2
NO SQL
DETERMINISTIC
PARAMETER STYLE SQL
CALLED ON NULL INPUT
EXTERNAL NAME 'CS!my_yyyymmdd_to_date2!./my_yyyymmdd_to_date2.c'
;

C Function Definition

The following user-defined function, my_yyyymmdd_to_date2.c, validates whether the argument character string in YYYYMMDD format is a valid date. It returns either a date or returns a NULL if the string is not a valid date.

/*
   my_yyyymmdd_to_date2.c
   Teradata User Defined Function (UDF)
   Calling
   -------
   my_yyyymmdd_to_date2(date_str);
   SELECT my_yyyymmdd_to_date2('20130423') AS ValidDate;
   Parameters
   ----------
   date_str
        Character string containing date to be validated
   UDF Compilation
   ---------------
   REPLACE FUNCTION my_yyyymmdd_to_date2
        (
            InputDate VARCHAR(8)
        )
    RETURNS DATE
    LANGUAGE C
    NO SQL
    DETERMINISTIC
    PARAMETER STYLE SQL
    EXTERNAL NAME 'CS!my_yyyymmdd_to_date2!./my_yyyymmdd_to_date2.c'
    ;
*/
/*	Must define SQL_TEXT before including "sqltypes_td	"*/
#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"
#include "stdio.h"
#include "string.h"
#define IsNull -1
#define IsNotNull 0
#define NoSqlError "00000"
#define YYYYMMDD_LENGTH 8
#define ERR_RC 99
void my_yyyymmdd_to_date2
(
     VARCHAR_LATIN      *InputDateString
     ,DATE              *result
     ,int               *inputDateStringIsNull
     ,int               *resultIsNull
     ,char              sqlstate[6]
     ,SQL_TEXT          extname[129]
     ,SQL_TEXT          specificname[129]
     ,SQL_TEXT          error_message[257]
)
{
     char input_integer[30];
     int  year_yyyy;
     int  month_mm;
     int  day_dd;
     char day_char[3];
     char month_char[3];
     char year_char[5];
     int  in_len,i;
     /* Return Nulls on Null Input */
     if ((*inputDateStringIsNull == IsNull))
     {
          strcpy(sqlstate, "22018") ;
          strcpy((char *) error_message, "Null value not allowed.") ;
          *resultIsNull = IsNull;
          return;
     }
     in_len = strlen(InputDateString);
     if ( in_len != YYYYMMDD_LENGTH )
     {
          *result = ( 1 * 10000 ) + ( 12 * 100) + 1;
          *resultIsNull = IsNull;
          strcpy((char *) sqlstate, "01H01");
          strcpy((char *) error_message,
          "InputDateString is of wrong length, must be in YYYYMMDD format");
          return;
     }
     if ( in_len != YYYYMMDD_LENGTH )
     {
          *result = ( 1 * 10000 ) + ( 12 * 100) + 2;
          return;
     }
     strcpy(input_integer , (char *) InputDateString);
     for (i = 0; i<in_len; i++)
     {
          if (input_integer[i] < '0' || input_integer[i] > '9')
          {
               *result = ( 1 * 10000 ) + ( 1 * 100) + 3;
               return;
          }
          else
          {
              input_integer[i] = tolower(input_integer[i]);
          }
     }
     sprintf(year_char,"%c%c%c%c",input_integer[0],input_integer[1],input_integer[2],
     input_integer[3]);
     sprintf(month_char,"%c%c",input_integer[4],input_integer[5]);
     sprintf(day_char,"%c%c",input_integer[6],input_integer[7]);
     year_yyyy	= atoi(year_char);
     month_mm	= atoi(month_char);
     day_dd		= atoi(day_char);
     /*	Format output_date in internal Teradata format ((YEAR - 1900) * 10000 ) +
     (MONTH * 100) + DAY	*/
     *result = (( year_yyyy - 1900 ) * 10000 ) + ( month_mm * 100) + day_dd;
}

BTEQ Script To Call the User-defined Function

This BTEQ script calls the user-defined function and tests it by selecting from the my_yyyymmdd_to_date2.sql table:

.SET WIDTH		240
.logon TDSystem/MyUserId,MyPass
.REMARK 'Building External UDF';
DATABASE Test_Database;
.RUN FILE=./my_yyyymmdd_to_date2.sql
.REMARK 'Testing External UDF';
SELECT Test_Database.my_yyyymmdd_to_date2('20130422')     AS ValidDate;
SELECT Test_Database.my_yyyymmdd_to_date2('201304')       AS ValidDate;
SELECT Test_Database.my_yyyymmdd_to_date2(NULL)           AS ValidDate;
.REMARK 'Finished testing External UDF';
.LOGOFF
 .EXIT 0

Linux Command To Call the BTEQ Script

The following Linux commands call the BTEQ script:

bteq bld_xudfs.btq 2>&1 | tee bld_xudfs.btq.i1.out