converting string to decimal

[pnchowdary]

Hi Guys,

Does any one know of any datastage function that converts a given string into a decimal number?

Code: Select all

For Example :-

"1943.100" is the input string that needs to be converted to decimal, so that I can apply further arithmetic operations on it.

I know of indirect techniques to achieve the same task, but I wanted to know whether there were any functions to do it directly.

Any help would be greatly appreciated.

[roy]

Hi,
Check the manuals for conversion routines (Iconv/Oconv)if you must or browse your built in routines.
Bare in mind implicit conversion will occur if needed and there are also routines for arithmetic operations on character type columns like SADD and such.

IHTH,

[pnchowdary]

Hi Roy,

Thank you very much for your input. However, I tried to use SADD function in the transformer and it gives an error. Therefore, I assume that it can be used only in the routines and not in the transformer. Could you please confirm whether my assumption is correct.

[ArndW]

DataStage will do the conversion for you automatically. If you have an input VARCHAR(10) column called MyString containing the value "1943.100" you can create an output column in a transform called MyNumber, you can do a derivation such as "In.Mystring*3.14159" and it would convert for you automatically.

[Sainath.Srinivasan]

In DataStage, you can do arith computations on char field as long as they hold numeric values.

You need SADD only for larger values.

[pnchowdary]

Thank you very much for all your responses. :D

[ray.wurlod]

SADD did not give you an error. Your job would have compiled correctly with SADD(x,y) in an expression.

What you saw was the expression being red, which indicates that the expression editor's parser discovered a syntax problem. This is because the SADD function - which is a legal BASIC function - does not appear in the DSParams file, from which the expression editor's parser discovers its list of "known" functions.

If you want to use the string math functions, you can add SADD, SSUB, SMUL and SDIV into the DSParams file on the server. Sting math functions give unlimited precision.

However, type casting is automatic in server jobs, as Arnd stated, so you don't need to convert string to decimal - you can just use regular arithmetic operators.

[kumar_s]

In server dont we have StringToDecimal() as like Parallel???

regards
kumar

[ray.wurlod]

In server we don't need StringToDecimal().

[chulett]

Server != Parallel.

[kumar_s]

In server we don't need StringToDecimal().

i this ment with intention of implicit conversion

[roy]

I think it was indeed ment as having the implicit conversion in mind for server jobs.

[chulett]

Agreed.

[pnchowdary]

Hi Ray,

You exactly hit the nail on the head. When I put in SADD(x,y) in the transformer, it was showing red color. Now, I understand why it did that and how to overcome that.

Thank you very much for clarifying it for me. :D