PX 6.0 String to Decimal
Moderators: chulett, rschirm, roy
PX 6.0 String to Decimal
Hi All,
How can i convert a String(1234.567) to Decimal(1234.567) ?
Input Column defined as: Varchar(8)
Output Column defined as: Decimal(7,3)
StringToDecimal(convert(".", "", Link.ColumnName)/1000) gives 1234.000
The decimal part is not coming.
Thanks in advance
Have a good weekend all
Jay
How can i convert a String(1234.567) to Decimal(1234.567) ?
Input Column defined as: Varchar(8)
Output Column defined as: Decimal(7,3)
StringToDecimal(convert(".", "", Link.ColumnName)/1000) gives 1234.000
The decimal part is not coming.
Thanks in advance
Have a good weekend all
Jay
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What did you expect to get?
Examine your expression from the inside outwards.
You have "1234.567" in a VarChar(8) as source.
The first thing you do is to remove the ".". You would now have "1234567".
The next thing you try to do is divide "1234567" (a character string) by 1000 (an integer). Is this really valid? If it is valid - that is, if DataStage is kind enough to perform an implicit CAST of "1234567" as INTEGER - then the result of the integer arithmetic is 1234.
I believe that this is where the problem arises.
What happens if you simply apply StringToDecimal to the original input string?
What happens if you divide by 1000.0 rather than by 1000?
What happens if you perform the StringToDecimal conversion before dividing by 1000.0?
Please post your results.
Examine your expression from the inside outwards.
You have "1234.567" in a VarChar(8) as source.
The first thing you do is to remove the ".". You would now have "1234567".
The next thing you try to do is divide "1234567" (a character string) by 1000 (an integer). Is this really valid? If it is valid - that is, if DataStage is kind enough to perform an implicit CAST of "1234567" as INTEGER - then the result of the integer arithmetic is 1234.
I believe that this is where the problem arises.
What happens if you simply apply StringToDecimal to the original input string?
What happens if you divide by 1000.0 rather than by 1000?
What happens if you perform the StringToDecimal conversion before dividing by 1000.0?
Please post your results.
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.
hi Jay,
The right formula is :
StringToDecimal(convert(".", "", Link.ColumnName))/1000.
In 6 version (I don't know if it works with 7 version) of datastage you can also use a transformer to convert a varchar into decimal without any convertion formula like 'stringtodecimal'. Just change the data type from Varchar(8) to decimal(7,3) in a transformer stage.
Bye.
Matthieu
The right formula is :
StringToDecimal(convert(".", "", Link.ColumnName))/1000.
In 6 version (I don't know if it works with 7 version) of datastage you can also use a transformer to convert a varchar into decimal without any convertion formula like 'stringtodecimal'. Just change the data type from Varchar(8) to decimal(7,3) in a transformer stage.
Bye.
Matthieu
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Matthieu, are you talking about parallel jobs? It's my understanding that - unless you're using the "BASIC Transformer" stage - implicit data type conversions do not occur; this is a behaviour found in server jobs only.
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.
Yes I'm talking about PX job. As strange as it seems it works with Datastage 6.0.1 version (I don't know with 7 version) ! We tried this after many problems with the conversion from string to decimal. About 5% of the datas with decimal values were wrong when a sign was included in the numeric field. The kind of error was :
-00001234,567 -> -00001234,566 (!!!)
Good week end.
Matthieu
-00001234,567 -> -00001234,566 (!!!)
Good week end.
Matthieu
Almost there
Hi Ray/Matthieu,
Here are the results:
StringToDecimal(value)- 1234.000
StringToDecimal(convert(".", "", value)/1000.0) = 1234.567
(if num = 12345.67 then result = 1234.000)
StringToDecimal(convert(".", "",value))/1000.0
= 1234.567
(if num = 12345.67 then result = 1234.567)
What Ray was saying is right. The value was being considered as an Integer instead of a Decimal.
On dividing by 1000.0 we are forcing the result to be a decimal.
Now i have to figure out a way of calculating the number of decimal places. Any ideas ?
Thanks a ton
Jay
Here are the results:
StringToDecimal(value)- 1234.000
StringToDecimal(convert(".", "", value)/1000.0) = 1234.567
(if num = 12345.67 then result = 1234.000)
StringToDecimal(convert(".", "",value))/1000.0
= 1234.567
(if num = 12345.67 then result = 1234.567)
What Ray was saying is right. The value was being considered as an Integer instead of a Decimal.
On dividing by 1000.0 we are forcing the result to be a decimal.
Now i have to figure out a way of calculating the number of decimal places. Any ideas ?
Thanks a ton
Jay
You know, I don't get it.
Why is it impossible to do a simple StringToDecimal(value)?
If you have an input value of "12345.689", given enough space (8,3 at a minimum), it would properly translate.
I have personally tested this on a 7.x box, and did not encounter any problem. Is there a flaw with 6.x that I am conveniently forgetting here?
I do know that decimal math is pretty bad (lacking) with EE 6.x (and fixed for 7.x), but is conversion also this bad?
Why is it impossible to do a simple StringToDecimal(value)?
If you have an input value of "12345.689", given enough space (8,3 at a minimum), it would properly translate.
I have personally tested this on a 7.x box, and did not encounter any problem. Is there a flaw with 6.x that I am conveniently forgetting here?
I do know that decimal math is pretty bad (lacking) with EE 6.x (and fixed for 7.x), but is conversion also this bad?
hi T42,
For Varchar to Decimal(8,3), if i do StringToDecimal("12345.678")
it gives 12345.000
As far as calculating the divisor is concerned i am doing the following
pwr(10.0,len(field(value,".",2)))
All but 1 row pass thru. It bombs when input value is:
12345.678.
Error is:
APT_CombinedOperatorController,0: Requirements Failure: APT_Decimal::assignFrom: src (12345.6) out of range for decimal with precision 7 and scale 3
Thanks
Jay
For Varchar to Decimal(8,3), if i do StringToDecimal("12345.678")
it gives 12345.000
As far as calculating the divisor is concerned i am doing the following
pwr(10.0,len(field(value,".",2)))
All but 1 row pass thru. It bombs when input value is:
12345.678.
Error is:
APT_CombinedOperatorController,0: Requirements Failure: APT_Decimal::assignFrom: src (12345.6) out of range for decimal with precision 7 and scale 3
Thanks
Jay
Functional requirement changed
Hi All
I think i am good to go.
I wont be getting such big numbers in the input after all....
Its an hourly rate, so i am expecting it to be small...
Thanks all for your help...
Jay
I think i am good to go.
I wont be getting such big numbers in the input after all....
Its an hourly rate, so i am expecting it to be small...
Thanks all for your help...
Jay
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Ah, I see the problem here. You are passing an integer in that form. When you create a new Decimal field, just type this in the transformer:Jay wrote:hi T42,
For Varchar to Decimal(8,3), if i do StringToDecimal("12345.678")
it gives 12345.000
12345.678
It will automatically pass the entire value as Decimal.
I have tried your example with 7.1r2, and it behave as I expected, "0012345.678" if I declare the field as a [10,3].
I would advise that you convince your company to upgrade to DataStage 7.1r2, or 7.5a. 7.5x2 is specifically Windows Server-based EE. There are a number of Decimal field flaws that have been fixed since 6.x, especially Decimal math.