Connectivity

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-11-2006
05:59 AM

03-11-2006
05:59 AM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-16-2006
06:59 PM

03-16-2006
06:59 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-17-2006
12:12 AM

03-17-2006
12:12 AM

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[6])

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[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.

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[6])

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[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.