Modify Stage Decimal conversion

[juancho]

Hello!

Is there a way to convert, using modify stage, a string field with the following data:

12034

to decimal 5,2? that is, to 120,34

the key here is using only modify stage because i need to pass the conversion using parameter (i am using one job for several files)

Thanks!

[hiral.chauhan]

Hello!

Is there a way to convert, using modify stage, a string field with the following data:

12034

to decimal 5,2? that is, to 120,34

So you mean you would like to convert 12034 which is a varchar to 120.34 which is a decimal(5,2) ?

-Hiral

[hiral.chauhan]

If you're trying to convert a varchar to decimal in Modify stage you need to use the following function:

decimal_from_string

decimal_col:decimal = decimal_from_string [ceil] (source_col)

For more information on this function you can refer Parallel job developer guide.

Hope that Helps.

-Hiral

[juancho]

If you're trying to convert a varchar to decimal in Modify stage you need to use the following function:

decimal_from_string

decimal_col:decimal = decimal_from_string [ceil] (source_col)

For more information on this function you can refer Parallel job developer guide.

Hope that Helps.

-Hiral

I am trying to use that, but since the string does NOT have any dots nor commas, the function just does not recognize it as the number i need, in fact, it complains that the number does not fit inside the definition, wich is OK since 5,2 is a decimal of the format 999.99.
Then when i try to convert 99999 to decimal 5,2, datastage understands 99999.99 instead of the number i need.

Is there any way to do concatenation or something similar using modify stage functions? because i was thinking maybe two substrings and a constant could do the trick, but i have found no example of it anywhere..

I think that if concatenation is possible i would be able to do

substring(first 3 digits) : "." : substring(last 2 digits)

Any ideas?

[chulett]

Couldn't you just divide it by 100?

[juancho]

Couldn't you just divide it by 100? ...

That is a great idea that i tried to implement in the modify stage but it seems not doable.

"<field> / 100" throws a syntax error... do you know the correct syntax?

[chulett]

I don't believe you can manipulate data in the Modify stage, only drop/keep columns and convert data types. You'd need a Transformer to do the division, as far as I know, your syntax would be fine there. Or you can try the Div() function.

[juancho]

I don't believe you can manipulate data in the Modify stage, only drop/keep columns and convert data types. You'd need a Transformer to do the division, as far as I know, your syntax would be fine there. Or you can try the Div() function.

My limitation is that i HAVE to use only modify stage, ill try DIV and get back to you, thanks!

[chulett]

Hmmm... nobody should HAVE to use only a Modify stage, as Ray would say - Resist Stupid Requirements! And I think Div() is only valid in a Transformer. If your only recourse is to divide the number by 100 because a lack of delimiters then I believe your new limitation will be that you'll HAVE to do that in a transformer.

[juancho]

Hmmm... nobody should HAVE to use only a Modify stage, as Ray would say - Resist Stupid Requirements! And I think Div() is only valid in a Transformer. If your only recourse is to divide the number by 100 because a lack of delimiters then I believe your new limitation will be that you'll HAVE to do that in a transformer.

Heh, i know the requirements are silly but its the only way i know that allows me to process metadata using schema files and parameters, i have like 200 raw files i have to read, extract header and trailer, then convert to a dataset and load to an oracle file.

I have everything done automagically, header and trailer extraction and loading to oracle only take two jobs and parameters some parameters, what i was missing is this only step, convert the decimal numbers to something we can use... maybe i'll ask the user to give me the numbers with the dots, but i think that will be hard to do.

The other option is to do 200 jobs...

thanks for the inputs

[chulett]

Well... at least we now have the reasoning behind your 'requirement'.