Connectivity

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.
2 REPLIES 2

Re: How to use Decimal(15,2)

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 function

output = cast(input * 2 as decimal(18,2))

this should work.

please let me know if it does not work.

Re: How to use Decimal(15,2)

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 as
CREATE 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 like
void *udf_m_2(DECIMAL8 *in_val, DECIMAL8 *result, char sqlstate)

or for PARAMETER STYLE SQL
void *udf_m_2(DECIMAL8 *in_val, DECIMAL8 *result,
int *in_val_NULL_indicator, int *result_NULL_indicator, char sqlstate,
SQL_TEXT extname,
SQL_TEXT specific_name,
SQL_TEXT error_message)

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.