UDF - pass dates to create factor

Extensibility
Fan

UDF - pass dates to create factor

i am fairly new to creating functions in teradata. i want to stress that i tried to create a simplistic c program to perform this code. i dont know if it is right or not. I attempted to create a function that will pass dates to determine a factor. i am having issues with the function and was hoping someone could look at it. First, i wil show you what i was trying to do in a basic module and then pass values into the function as a second example.

Module (pass values below to determine the factor) seems pretty simple

EarnedExposProRata(PolEff, PolExp, PeriodStart,PeriodEnd,ExposYrStart,ExposYrEnd,CycleDate)

/*if one of these are 0 then skip over all other code */
result=if PolEff>PeriodEnd,0
else if PolExp
define NewPeriodEnd=if cycleDatedefine NewExposYrEnd=if cycleDatedefine TotPoldaysExposYr=min(PolExp,NewExposYrEnd)-max(PolEff,ExposYrStart)+1

daysInPeriod=Min(PolExp,NewPeriodEnd)-Max(periodStart,PolEff)+1

result=daysInPeriod/TotPoldaysExposYr

Now an example on how this would work
EarnedExposProRata(7/1/2009, 7/1/2010, 1/1/2010,2/28/2010,1/1/2010,12/31/2010,2/28/2010)
/*if one of these are 0 then skip over all other code */
result=if PolEff>PeriodEnd,0
else if PolExp
define NewPeriodEnd=if cycleDatedefine NewExposYrEnd=if cycleDatedefine TotPoldaysExposYr=min(PolExp,NewExposYrEnd)-max(PolEff,ExposYrStart)+1 2/28/2010-1/1/2010+1
59.00
daysInPeriod=Min(PolExp,NewPeriodEnd)-Max(periodStart,PolEff)+1 2/28/2010-1/1/2010+1
59.00
result=daysInPeriod/TotPoldaysExposYr 59/59=1.000
1.000000

1.000 would be the factor

Next I perform a run file in bteq to create my function performing the following code:
bteq

.LOGON server/username, password
database AWA;

REPLACE FUNCTION EarnedExposProRata(
PolEff,
PolExp,
PeriodStart,
PeriodEnd,
ExposYrStart,
ExposYrEnd,
CycleDate
)
RETURNS INTEGER
LANGUAGE C
NO SQL
EXTERNAL NAME 'CS!EarnedExposProRata!c:\temp\EarnedExposProRata.c!F!EarnedExposProRata'
PARAMETER STYLE TD_GENERAL;
.logoff

lastly, my function performs what was shown in the module

#include "sqltypes_td.h"
#include
#include

#define IS_NULL -1
#define IS_NOT_NULL 0
#define UDF_OK "00000"
#define DEFAULT_DATE(d) (d <= 101) /* 1900-01-01 */

/*
* date format: ((year - 1900) * 10000 + (month * 100) + day) (INTEGER)
*/

void EarnedExposProRata(DATE *PolEff,
DATE *PolExp,
DATE *PeriodStart,
DATE *PeriodEnd,
DATE *ExposYrStart,
DATE *ExposYrEnd,
DATE *CycleDate,
int *PolEff_indicator,
int *PolExp_indicator,
int *PeriodStart_indicator,
int *PeriodEnd_indicator,
int *ExposYrStart_indicator,
int *ExposYrEnd_indicator,
int *CycleDate_indicator,
int *result_indicator,
char sqlstate[6],
SQL_TEXT extname[129],
SQL_TEXT specific_name[129],
SQL_TEXT error_message[257])
{
/*short total_months;*/
DateADT NewPeriodEnd;
DateADT NewExposYrEnd;
DateADT TotPoldaysExposYr;
short daysInPeriod;
short result;

if (*PolEff_indicator == IS_NULL || *PolExp_indicator == IS_NULL || *PeriodStart_indicator == IS_NULL || *PeriodEnd_indicator == IS_NULL || *ExposYrStart_indicator == IS_NULL || *ExposYrEnd_indicator == IS_NULL || *CycleDate_indicator == IS_NULL)
{
*result_indicator = IS_NULL;

(void) strcpy(sqlstate, "U0001");
(void) strcpy((char *) error_message, "NULL values not allowed.");

return;
}

if (DEFAULT_DATE(*PolEff))
{
*result_indicator = IS_NULL;

(void) strcpy(sqlstate, UDF_OK);
(void) strcpy((char *) error_message, " ");

return;
}

/*total_months = ((YEAR(*eval_date) * 12) + MONTH(*eval_date)) -
((YEAR(*start_date) * 12) + MONTH(*start_date));*/

if (*PolEff > *PeriodEnd)
result= 0;
goto expproend;

else if (*PolExp < *PeriodStart)
result = 0;
goto expproend;

if (*CycleDate < *periodend )
NewPeriodEnd = CycleDate;
else
NewPeriodEnd = PeriodEnd;

if (*CycleDate < *ExposYrEnd )
NewExposYrEnd = *CycleDate;
else
NewExposYrEnd = *ExposYrEnd;

if (*PolExp < NewExposYrEnd )
TotPoldaysA = PolExp;
else
TotPoldaysA = NewExposYrEnd;

if (*PolEff > *ExposYrStart )
TotPoldaysB = PolEff;
else
TotPoldaysB = ExposYrStart;

TotPoldaysExposYr = (TotPoldaysA - TotPoldaysB) + 1;

if (*PolExp < *NewPeriodEnd )
DaysA = PolExp;
else
DaysA = NewPeriodEnd;

if (*periodStart > *PolEff )
DaysB = periodStart;
else
DaysB = PolEff;

daysInPeriod = (DaysA - DaysB) + 1;

if (daysInPeriod <= 0 )
result = 0;
else
result = (daysInPeriod / TotPoldaysExposYr);

expproend:

(void) strcpy(sqlstate, UDF_OK);
(void) strcpy((char *) error_message, " ");
}

/*
* EarnedExposProRata UDF - EarnedExposProRata.c
*/

can anyone tell me what i am doing wrong ?
2 REPLIES
Fan

Re: UDF - pass dates to create factor

i have gotten a lot farther but am receiving the following error:

7508: Corrupt stack frame for UDF/XSP/UDM

i am searching the forums but can't seem to figure out where i am going wrong
Teradata Employee

Re: UDF - pass dates to create factor

Your function prototype needs a pointer to the result field, between the input parameters and the indicator fields. And don't declare the result as a local variable.