Hi,
I am getting alpha numeric value in a String column ex"1232123 ABZC".
Now I need to extract these two values to seperate columns.
I tried Field function to extract these two values seperatly.
But when I try to convert the first part of the string to numeric value (i used StringToDecimal funtion ) the job gives compilation error saying
"decimal_to_string( <line no> ,<character position>) "
"Possible range limitation"
and job does not compile.
Same job compiles with out an problem once you remove the function and compile succees fully and again use the function and compile. This time job compiles with out problem.
There is a funtion to convert numbers to integer. But this function takes numeric values and to convert to input to numeric I need to use StringTo Decimal and the story comes square one.
Can anybody tell why this happens and what is the solution.
handling of String to Integer conversion
Moderators: chulett, rschirm, roy
handling of String to Integer conversion
Happy DataStaging
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you're in a position to use BASIC functions, these are the ones you want.
- Oconv(TheString, "MCN") keeps only the numeric characters.
Oconv(TheString, "MC/N") keeps only the non-numeric characters.
Oconv(TheString, "MCA") keeps only the alphabetic characters.
Oconv(TheString, "MC/A") keeps only the non-alphabetic characters.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: handling of String to Integer conversion
I am sorry if I ask you more details. From experience, when you design PX job data type and there length are so important. Can you give more details like what are the data type output fields. Because once converted into decimal using StringToDecimal, you might want to do casting using Integer function.
Thanks
[quote="dsxdev"]Hi,
I am getting alpha numeric value in a String column ex"1232123 ABZC".
Now I need to extract these two values to seperate columns.
I tried Field function to extract these two values seperatly.
But when I try to convert the first part of the string to numeric value (i used StringToDecimal funtion ) the job gives compilation error saying
"decimal_to_string( <line no> ,<character position>) "
"Possible range limitation"
and job does not compile.
Same job compiles with out an problem once you remove the function and compile succees fully and again use the function and compile. This time job compiles with out problem.
There is a funtion to convert numbers to integer. But this function takes numeric values and to convert to input to numeric I need to use StringTo Decimal and the story comes square one.
Can anybody tell why this happens and what is the solution.[/quote]
Thanks
[quote="dsxdev"]Hi,
I am getting alpha numeric value in a String column ex"1232123 ABZC".
Now I need to extract these two values to seperate columns.
I tried Field function to extract these two values seperatly.
But when I try to convert the first part of the string to numeric value (i used StringToDecimal funtion ) the job gives compilation error saying
"decimal_to_string( <line no> ,<character position>) "
"Possible range limitation"
and job does not compile.
Same job compiles with out an problem once you remove the function and compile succees fully and again use the function and compile. This time job compiles with out problem.
There is a funtion to convert numbers to integer. But this function takes numeric values and to convert to input to numeric I need to use StringTo Decimal and the story comes square one.
Can anybody tell why this happens and what is the solution.[/quote]
Re: handling of String to Integer conversion
Hi,
if Your data always looks like "1232123 ABZC" then You can also try the ColumnImport Stage. There You can specify an input column that will be split into output columns. Much like reading from a sequential file. In Your case specify "whitespace" as separator, define an integer and a varchar as output columns and You should be all set.
Hope that helps,
Chris
if Your data always looks like "1232123 ABZC" then You can also try the ColumnImport Stage. There You can specify an input column that will be split into output columns. Much like reading from a sequential file. In Your case specify "whitespace" as separator, define an integer and a varchar as output columns and You should be all set.
Hope that helps,
Chris
Re: handling of String to Integer conversion
I am using this function in a transformer and my experience says BASIC transformer reduces performance drastically.dsxdev wrote:Hi,
I am getting alpha numeric value in a String column ex"1232123 ABZC".
Now I need to extract these two values to seperate columns.
I tried Field function to extract these two values seperatly.
But when I try to convert the first part of the string to numeric value (i used StringToDecimal funtion ) the job gives compilation error saying
"decimal_to_string( <line no> ,<character position>) "
"Possible range limitation"
and job does not compile.
Same job compiles with out an problem once you remove the function and compile succees fully and again use the function and compile. This time job compiles with out problem.
There is a funtion to convert numbers to integer. But this function takes numeric values and to convert to input to numeric I need to use StringTo Decimal and the story comes square one.
Can anybody tell why this happens and what is the solution.
My input column type is varchar(26). I need the split this field into two parts one Numeric and remaining.This numeric field can vary from 1 to 26 digit long. Usually I get numbers of 12 to 16 digit long in this field.
I am extracting teh field and Type casting it to BigInt(18) then also this problem persists. As soon as I introduce this StringToDecimal function I get this problem.
Any inputs would be helpful.
Happy DataStaging