Skip to Content

Conversion functions in BODS_Part3

In this section I will explain remaining conversion functions available in BO data services. These functions are mainly.

  1.    To_decimal
  2.    To_decimal_ext
  3.    Varchar_to_long
  4.    Long_to_varchar.

TO_DECIMAL

This function will convert a string value into decimal value based on the value given.

The syntax for this function is given below.

To_decimal(Input string,decimal_sep,thousand_sep,scale)

Input string

Input number string

Decimal_sep

Separate decimal component from whole number component

Thousand_sep

Separates thousands from hundreds.

Scale

Number of digit right to the decimal value to be return.

If the input string is invalid then the output this function will be zero.

E.g. To_decimal(’19,130.234’,’.’,’,’,3)=19130.234

TO_DECIMAL_EXT

This function will convert a string value into decimal and include precision as a parameter. It will support decimal data type up to 96 precision.

The syntax for this function is given below.

To_decimal_ext(Input string,decimal_sep,thousand_sep,precision,scale)

Input string

Input number string

Decimal_sep

Separate decimal component from whole number component

Thousand_sep

Separates thousands from hundreds.

Precision

Total number of digit in return value.

Scale

Number of digit right to the decimal value to be return.

If the input string is invalid then the output this function will be zero.

E.g. To_decimal(’19,130.234’,’.’,’,’,38,3)=19130.234

VARCHAR_TO_LONG

This function will convert a string into long data type column.

This function help full when the string size is too high, consider an example if the string having size 25000 and this column mapped to target column having data type varchar(max). In bods the varchar(max) is  read as long data type. So for loading this data this function is very helpful.

Varchar_to_long(column name).

LONG_TO_VARCHAR

This function will convert a data type column value from long to varchar.

The syntax for this function will be.

Long_to_varchar(column_name,max_size,Start_pos)

column_name

Long Column to be converted into varchar

max_size

Max size of the string after conversion

Start_pos

Start position from that position the conversion start. Default value is 1 and if it is negative that means starting position counted backward

Consider the data type of the column in database is varchar(max) ,when importing this column in to data service ,the dataservice will read this column as long data type.So to apply some string  function to this column convert it to varchar by using long_varchar function.

Regards

Asgar

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. A Junaid

    Dear Asgar,

    Can you please mention an example for the LONG_TO_VARCHAR conversion.

    Long_to_varchar(column_name,max_size,Start_pos) =  long_to_varchar( QUERY_SHIPMENT_AGENCY_TD.LDV_DESCRIPTION, 60, 1) I tried but it gave me error BODI-1111305.

    Thanks in advance

     

     

    Regards

    A Junaid

    (0) 
  2. Garima Kanojiya

    Hi Asgar,

     

    what if I use cast function to convert long to varchar ? will it be having the same effect as that of long_to_varchar or anything different?

     

    Thanks.

    (0) 

Leave a Reply