|
|
|
Forum Newbie
Group: Forum Members
Last Login: 8/21/2008 9:20:19 PM
Posts: 8,
Visits: 34
|
|
We will generate some reports from TeraData, due to the business logic is too complex, we may need to create function in TeraData, and use them as below:
select function(V_DIM_ACCOUNT.Acct_Num) from V_Dim_Account;
As I can see now, TeraData seems don't support using SQL to write function but procedure, but procedure can't fit our requirement because I can't use it in a simple select sql. Is there anyone have writen any functions in Teradata before, can you send some samples to me about this?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 9:19:53 PM
Posts: 469,
Visits: 463
|
|
You probably need a UDF, these are programs written in C/C++ and installed into the database server, so you will be able to do things like SELECT MYFUNC(acct) FROM ACCOUNT; etc ...
For more details you can have a look into the UDF manual & Orange book. The later even have descriptive examples of how to create one ... Your can download it from the Teradata at your service website or your NCR support personnel should be able to get it for you.
Here's a small UDF I had made in the past ....
Do remember that you will need a C compiler in atleast one of the PE nodes to get it to work.
/* File D:\UDFGadgets\extractNums.c Use this in the SQL definition below*/ #define SQL_TEXT Latin_Text #include "sqltypes_td.h"
#include
#define ISNULL -1
#define ISNOTNULL 0
#define NOSQLERROR "00000"
void extractNums
(
VARCHAR_LATIN *inputStr
,VARCHAR_LATIN *outputStr
,int *inputStrIsNull
,int *outputStrIsNull
,char sqlstate[6]
,SQL_TEXT extname[129]
,SQL_TEXT specific_name[129]
,SQL_TEXT error_message[257]
)
{
VARCHAR_LATIN *outputStrStart;
if ((*outputStrIsNull = *inputStrIsNull) == ISNULL)
return;
*(outputStrStart = outputStr) = '\0';
while (*inputStr != '\0')
{
if (*inputStr >= '0' && *inputStr <= '9') *outputStr++ = *inputStr;
else if (outputStr != outputStrStart && *(outputStr-1) != ' ') *outputStr++ = ' ';
inputStr++;
}
if (outputStr != outputStrStart && *(outputStr-1) == ' ')
*(outputStr-1) = '\0';
else *outputStr = '\0';
strcpy(sqlstate, NOSQLERROR);
strcpy((char *) error_message, " ");
}
/* End of C program */
-- SQL to install the UDF ... Remember to update the path to the C file.
REPLACE FUNCTION extractNums
(
str VARCHAR(900)
)RETURNS VARCHAR(900)
LANGUAGE C
NO SQL
SPECIFIC extractNums
EXTERNAL NAME 'CS!extractNums!D:\UDFGadgets\extractNums.c'
PARAMETER STYLE SQL;
|
|
|
|