All Forums Connectivity
Koentje 23 posts Joined 09/04
11 Mar 2006
How to use Decimal(15,2)

Can someone give me an example for a very simple UDF. I just want to have the C-code and the definition of the function.Name: UDF_Multiplyby2()Input: Decimal(15,2)Output: Decimal(15,2)To do: Multiply Input by 2, that's all.Teradata gives me always a stack error.I'm using DECIMAL8 as in- and output.Thank's a lot,Koen.

16 Mar 2006

you have to increase the output lenght. leave the input as is decimal (15,2)so the largest number you can provide is 9999999999999.99 set the output: Decimal(18,2) {I guess 18 is the max that is allowed}and when you do the multiplication use the cast functionoutput = cast(input * 2 as decimal(18,2))this should work.please let me know if it does not work.

Fred 1096 posts Joined 08/04
17 Mar 2006

Note that though the logic inside the function body may be "simple", you can't code the prototype as a "simple C function". In other words,DECIMAL8 *udf_m_2(DECIMAL8 *in_val){...}will not work.If you register the function asCREATE FUNCTION UDF_Multiplyby2(InVal DECIMAL(15,2)) RETURNS DECIMAL(15,2)... PARAMETER STYLE TD_GENERAL ... EXTERNAL NAME ... !udf_m_2'Then the function prototype would be something likevoid *udf_m_2(DECIMAL8 *in_val, DECIMAL8 *result, char sqlstate[6])or for PARAMETER STYLE SQLvoid *udf_m_2(DECIMAL8 *in_val, DECIMAL8 *result,int *in_val_NULL_indicator, int *result_NULL_indicator, char sqlstate[6],SQL_TEXT extname[129],SQL_TEXT specific_name[129],SQL_TEXT error_message[257])Then note that DECIMAL8 is a struct with a 32-bit unsigned and 32-bit signed integer. You may want to convert that to a native 64-bit integer type.

You must sign in to leave a comment.