issues with StringToDecimal function.

[dsxdev]

Hi,
In one of my jobs I am using StringToDecimal function. Input to this is a varchar column. Data in this column is of nature
"2314123 dafsdf adsfas 2132" or
"123123" or "123213231 SDFASD". It varies I need to extract the first numeric part and then convert it to Integer data type (BigInt preferably).

I am using this code.
I am assigning the value to a stage variable sgVar of decimal (26,2)
sgVar is

Code: Select all

 If IsNull(IPCOL)=1 Then 0 Else StringToDecimal(Field(Trim(IPCOL)," ",1,1)),"trunc_zero") 

When I compile the job I get this error message.

##I TUTL 000031 06:40:06(001) <main_program> The open files limit is 2000; raising to 2147483647.
##I TOSH 000002 06:40:06(002) <main_program> orchgeneral: loaded
##I TOSH 000002 06:40:06(003) <main_program> orchsort: loaded
##I TOSH 000002 06:40:06(004) <main_program> orchstats: loaded
##I TFSC 000001 06:40:06(007) <main_program> APT configuration file: /apps/Ascential/DataStage/Configurations/ADW_4_Node_Conf.apt
##E TBLD 000000 06:40:10(000) <main_program> Error when checking composite operator: Subprocess command failed with exit status 256
##E TFSR 000019 06:40:10(001) <main_program> Could not check all operators because of previous error(s)
##W TFCP 000000 06:40:10(002) <transform> Error when checking composite operator: The number of reject datasets "0"is less than the number of input datasets "1".
##W TFCP 000000 06:40:10(003) <transform> Error when checking composite operator: Function "decimal_from_string [line 1,120,character 46]" returns decimal, default precision is used
##W TFCP 000025 06:40:10(004) <transform> Error when checking composite operator: Possible range limitation
##W TFCP 000000 06:40:10(005) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".

and line 1120 is

1120: OutputLink.OPCOL = decimal_from_string(StageVar0_sgVar , InterVar0_200);

and these are the intermediate variables used by the transformer.

0245: string InterVar0_200;
0246: InterVar0_200 = "trunc_zero";

Can any body tell what could be the problem and is there any solution.

This same code sometimes works with out any problem job also compiles and runs properly but when ever I makes some changes in the transformer but not in the same transformation job starts giving problem.

Any help would be welcome.
[/code]

[pavankvk]

Hi there..

this is a problem with the StringToDecimal function..we did a work around by converting the value to a int and then multiplying it with 0.01.

dont know the cause,but its a problem with the function..

Hi,
In one of my jobs I am using StringToDecimal function. Input to this is a varchar column. Data in this column is of nature
"2314123 dafsdf adsfas 2132" or
"123123" or "123213231 SDFASD". It varies I need to extract the first numeric part and then convert it to Integer data type (BigInt preferably).

I am using this code.
I am assigning the value to a stage variable sgVar of decimal (26,2)
sgVar is

Code: Select all

 If IsNull(IPCOL)=1 Then 0 Else StringToDecimal(Field(Trim(IPCOL)," ",1,1)),"trunc_zero") 

When I compile the job I get this error message.

##I TUTL 000031 06:40:06(001) <main_program> The open files limit is 2000; raising to 2147483647.
##I TOSH 000002 06:40:06(002) <main_program> orchgeneral: loaded
##I TOSH 000002 06:40:06(003) <main_program> orchsort: loaded
##I TOSH 000002 06:40:06(004) <main_program> orchstats: loaded
##I TFSC 000001 06:40:06(007) <main_program> APT configuration file: /apps/Ascential/DataStage/Configurations/ADW_4_Node_Conf.apt
##E TBLD 000000 06:40:10(000) <main_program> Error when checking composite operator: Subprocess command failed with exit status 256
##E TFSR 000019 06:40:10(001) <main_program> Could not check all operators because of previous error(s)
##W TFCP 000000 06:40:10(002) <transform> Error when checking composite operator: The number of reject datasets "0"is less than the number of input datasets "1".
##W TFCP 000000 06:40:10(003) <transform> Error when checking composite operator: Function "decimal_from_string [line 1,120,character 46]" returns decimal, default precision is used
##W TFCP 000025 06:40:10(004) <transform> Error when checking composite operator: Possible range limitation
##W TFCP 000000 06:40:10(005) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "Decimal".

and line 1120 is

1120: OutputLink.OPCOL = decimal_from_string(StageVar0_sgVar , InterVar0_200);

and these are the intermediate variables used by the transformer.

0245: string InterVar0_200;
0246: InterVar0_200 = "trunc_zero";

Can any body tell what could be the problem and is there any solution.

This same code sometimes works with out any problem job also compiles and runs properly but when ever I makes some changes in the transformer but not in the same transformation job starts giving problem.

Any help would be welcome.
[/code]

[dsxdev]

Hi Pavan,
Can you tell me how did you convert the varchar field to Integer.
My requirement is also to convert the varchar to Integer.

There are two copies of the same job one job compiles and works and the second one does not even compile.

This problem has been there for sometime.

[mandyli]

Hi

As per my knowledge varchar or string to integer is implicitly conversion. So directly u can use string to integer


Thanks
Man

[dsxdev]

This can be done at any point but why does this happen is my question. and if we get any such messages how to know where the problem is