PX 6.0 String to Decimal

Jay · Post by **Jay** » Fri Jan 07, 2005 5:19 pm

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

ray.wurlod · Post by **ray.wurlod** » Fri Jan 07, 2005 5:39 pm

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.

mpouet · Post by **mpouet** » Fri Jan 07, 2005 6:29 pm

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

ray.wurlod · Post by **ray.wurlod** » Fri Jan 07, 2005 7:33 pm

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.

mpouet · Post by **mpouet** » Sat Jan 08, 2005 7:31 am

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

Jay · Post by **Jay** » Sat Jan 08, 2005 12:04 pm

hi,

i will let u know the exact results on Monday.

Thanks a lot
Jay

Jay · Post by **Jay** » Mon Jan 10, 2005 8:40 am

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

T42 · Post by **T42** » Mon Jan 10, 2005 10:03 am

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?

Jay · Post by **Jay** » Mon Jan 10, 2005 10:34 am

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

Jay · Post by **Jay** » Mon Jan 10, 2005 5:03 pm

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

ray.wurlod · Post by **ray.wurlod** » Mon Jan 10, 2005 8:26 pm

Its an hourly rate, so i am expecting it to be small...

Have you no ambition?!!

Jay · Post by **Jay** » Tue Jan 11, 2005 11:34 am

I will be galaxy hopping if i get 28000/hr....

T42 · Post by **T42** » Wed Jan 19, 2005 6:04 pm

Jay wrote:hi T42,

For Varchar to Decimal(8,3), if i do StringToDecimal("12345.678")

it gives 12345.000

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:

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.

DSXchange

PX 6.0 String to Decimal

PX 6.0 String to Decimal

Almost there

Functional requirement changed