Warning during execution of StringToDecimal Function
Moderators: chulett, rschirm, roy
Warning during execution of StringToDecimal Function
Hi,
I am having a char fiield of length 5( value is "001 ") populating to Decimal of 5. i am applying "string to decimal" and trim function combindly to remove the spaces and to convert to decimal.
I am giving as StringToDecimal(Trim("FieldName")). But i am getting warnings like
APT_CombinedOperatorController,0: A numeric string was expected, got "001 "; using Decimal, the default value.
If the input doesn't contain the spaces(Value is "00011"), then i am not getting these warnings..
Can any one help me.
I am having a char fiield of length 5( value is "001 ") populating to Decimal of 5. i am applying "string to decimal" and trim function combindly to remove the spaces and to convert to decimal.
I am giving as StringToDecimal(Trim("FieldName")). But i am getting warnings like
APT_CombinedOperatorController,0: A numeric string was expected, got "001 "; using Decimal, the default value.
If the input doesn't contain the spaces(Value is "00011"), then i am not getting these warnings..
Can any one help me.
Use TRIM() to remove the spaces that the StringToDecimal function can't handle.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
The TRIM will only work on VarChar and not CHAR columns.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
If your data type is VarChar and trim doesn't work then you don't have spaces in your string. But I looked at your code and
will not work, you need to remove the 2 double-quotes so that the value of the parameter is used, not it's name.StringToDecimal(Trim("FieldName"))
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
The option available in transformer (Also available project level).
If enabled, DataStage tries to combine all possible orchestrate operator to a single operator while compiling to reduce the over head.
Though the funcitons are given in an order, which should also work according to its own precedence, sometimes the trim and the StringToDecimal conversion may get combined. Hence it may work in other order.
If enabled, DataStage tries to combine all possible orchestrate operator to a single operator while compiling to reduce the over head.
Though the funcitons are given in an order, which should also work according to its own precedence, sometimes the trim and the StringToDecimal conversion may get combined. Hence it may work in other order.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Kumar, what does TRIM() on a CHAR(10) field containing "hello " return?kumar_s wrote:Trim also works for Char...
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
Hi Kumar,
{Edited}
What I wanted to point to was the "0x0" character also know as the ASCII NULL. What I typed was "char(10). this post has been edited to correct this error.
{/edited}
<begin RANT>
Trim() does not work on the 0x0 character. Additionally, Those characters either show up as spaces or are skipped in most text editors, unix command line dumps (using cat), log output from the peek stage and the DataStage view data page.
Try this:
have a char field with the "0x0" character, output <in.charfield> and trim(<in.charfield>) to a text file. Open the text file using a hex editor (Xvi32 is what I use). check the output.
The only function that works on "0x0" is the convert(char(000), " ",<in.charfield>). (posting from home, Syntax might be incorrect). Note the Space instead of the empty string. If an empty string is used and the value is assigned to a char column again, the removed characters are padded with the default APT_STRING_PADCHAR( which is "0x0").. Full Circle!!
<end RANT>
Beware of the 0x0 character.
{Edited}
What I wanted to point to was the "0x0" character also know as the ASCII NULL. What I typed was "char(10). this post has been edited to correct this error.
{/edited}
<begin RANT>
Trim() does not work on the 0x0 character. Additionally, Those characters either show up as spaces or are skipped in most text editors, unix command line dumps (using cat), log output from the peek stage and the DataStage view data page.
Try this:
have a char field with the "0x0" character, output <in.charfield> and trim(<in.charfield>) to a text file. Open the text file using a hex editor (Xvi32 is what I use). check the output.
The only function that works on "0x0" is the convert(char(000), " ",<in.charfield>). (posting from home, Syntax might be incorrect). Note the Space instead of the empty string. If an empty string is used and the value is assigned to a char column again, the removed characters are padded with the default APT_STRING_PADCHAR( which is "0x0").. Full Circle!!
<end RANT>
Beware of the 0x0 character.
Last edited by ameyvaidya on Mon Mar 13, 2006 3:21 am, edited 2 times in total.
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
Hi Ameyvaidya ,
Char is a takes 1 byte to store its each character. And if you assing char(10), Datastage assigns 10 byte for storage. If you have space in the field and if you use Trim function and store back to char field it again expects the 10 character to be stored. (And hence does nothing)
But if you use trim and store to varchar it will trim the space. (Obviously varchar by itself to this for you)
Trim will work for this particular scenario for this post.
Also pls look at what i have mentioned, Len(Trim(charfield)) will give you the length value without whitespace.
And you informed that you APT_PAD_CHAR is space.
Alright no try with the follwoing,
Define a char(10) in the input, and map to output with four column.
1. Len(feild_name) --> Integer Field
2. Len(Trim(field_name)) --> Integer field
3. Trim(field_name) --> Char Field
4. Trim(field_name) --> Varchar Field
Let say if your input is "Hello " ('Hello' -> 5 digit + 5 space)
For the first column you would get 10
For second column you would get 5
For third coulmn you get "Hello "
For fourth column you get "Hello"
And if you Pass this "Hello " to a varchar, you will get the spaces at the trailing end. (Considiring the space as a valid value)
Also try "Hello " (Just 2 space) in the input, this record will be rejected. Char(10) should be Char(10)
IHTH
Char is a takes 1 byte to store its each character. And if you assing char(10), Datastage assigns 10 byte for storage. If you have space in the field and if you use Trim function and store back to char field it again expects the 10 character to be stored. (And hence does nothing)
But if you use trim and store to varchar it will trim the space. (Obviously varchar by itself to this for you)
Trim will work for this particular scenario for this post.
Also pls look at what i have mentioned, Len(Trim(charfield)) will give you the length value without whitespace.
And you informed that you APT_PAD_CHAR is space.
Alright no try with the follwoing,
Define a char(10) in the input, and map to output with four column.
1. Len(feild_name) --> Integer Field
2. Len(Trim(field_name)) --> Integer field
3. Trim(field_name) --> Char Field
4. Trim(field_name) --> Varchar Field
Let say if your input is "Hello " ('Hello' -> 5 digit + 5 space)
For the first column you would get 10
For second column you would get 5
For third coulmn you get "Hello "
For fourth column you get "Hello"
And if you Pass this "Hello " to a varchar, you will get the spaces at the trailing end. (Considiring the space as a valid value)
Also try "Hello " (Just 2 space) in the input, this record will be rejected. Char(10) should be Char(10)
IHTH
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
Sorry About this..
In my above post, char(10) was incorrect.. i meant the hex character "0x0"
Its been a while.
The previous post has been edited
.
Sorry again.
In my above post, char(10) was incorrect.. i meant the hex character "0x0"
Its been a while.
The previous post has been edited
.
Sorry again.
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
Hha..
0x0 is Ascii Null.
Yes if you assign this you will get all funky characters depend upon you test editor.
Now if you use Trim on Char field, all the white space will be replaced with this Ascii null (0x0).
You may see those items as square of circle based on your text editor interpretation.
So if you use "Hello " you would get 'Hello' followed by 5 circles.
0x0 is Ascii Null.
Yes if you assign this you will get all funky characters depend upon you test editor.
Now if you use Trim on Char field, all the white space will be replaced with this Ascii null (0x0).
You may see those items as square of circle based on your text editor interpretation.
So if you use "Hello " you would get 'Hello' followed by 5 circles.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'