Warning during execution of StringToDecimal Function

Madhu1981 · Post by **Madhu1981** » Fri Mar 10, 2006 5:54 am

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.

ArndW · Post by **ArndW** » Fri Mar 10, 2006 6:02 am

Use TRIM() to remove the spaces that the StringToDecimal function can't handle.

Madhu1981 · Post by **Madhu1981** » Fri Mar 10, 2006 6:22 am

Hi I am using Trim Function,

Still i am getting the same errors.

I am using function as StringToDecimal(Trim("FieldName"))

Kindly Help me in avpoiding this warnings

ArndW · Post by **ArndW** » Fri Mar 10, 2006 6:55 am

The TRIM will only work on VarChar and not CHAR columns.

Madhu1981 · Post by **Madhu1981** » Fri Mar 10, 2006 7:34 am

Hi Andrw,

I have tried stripwhite spaces also.!! Still i am getting the same problem

Kindly help me

ArndW · Post by **ArndW** » Fri Mar 10, 2006 8:19 am

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

StringToDecimal(Trim("FieldName"))

will not work, you need to remove the 2 double-quotes so that the value of the parameter is used, not it's name.

kumar_s · Post by **kumar_s** » Sat Mar 11, 2006 1:04 am

Trim also works for Char.
Try to use Combinability Mode to fasle in the transformer property.

Madhu1981 · Post by **Madhu1981** » Sat Mar 11, 2006 1:25 am

Hi Kumar, Can you explain in detail. What does this combinability mode does and how it works when i make it to false

kumar_s · Post by **kumar_s** » Sat Mar 11, 2006 1:36 am

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.

ArndW · Post by **ArndW** » Sat Mar 11, 2006 1:45 am

kumar_s wrote:Trim also works for Char...

Kumar, what does TRIM() on a CHAR(10) field containing "hello " return?

kumar_s · Post by **kumar_s** » Sat Mar 11, 2006 1:48 am

ArndW wrote:
kumar_s wrote:Trim also works for Char...
Kumar, what does TRIM() on a CHAR(10) field containing "hello " return?

For a char field Len(Trim(field_name)) will return the count without whitspace.

ameyvaidya · Post by **ameyvaidya** » Sun Mar 12, 2006 1:13 am

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.

kumar_s · Post by **kumar_s** » Sun Mar 12, 2006 2:25 am

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

ameyvaidya · Post by **ameyvaidya** » Mon Mar 13, 2006 3:10 am

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.

kumar_s · Post by **kumar_s** » Mon Mar 13, 2006 3:46 am

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.