Number conversion

reddy · Post by **reddy** » Thu Oct 16, 2008 11:48 am

Hi Guyz,

I have a requirement as follows:

I have the source data in the following format:

Source

3001.23
200.2
2345
2323
1876.12

I want the data to be converted into the following format:

Target

3001.23
200.20
2345.00
2323.00
1876.12

Could someone help me with this issue. Thanks in advance.

DeepakCorning · Post by **DeepakCorning** » Thu Oct 16, 2008 12:10 pm

Declare the data type in the target as Decimal with a precision 2. As far as whtvr I have seen When you map it up and the insertion takes place it will automtically take care of the format.

reddy · Post by **reddy** » Thu Oct 16, 2008 12:15 pm

Hey thank you for quick reply but i already have it in place and even then iam not getting it. decimal(15,2)

Mike · Post by **Mike** » Thu Oct 16, 2008 12:26 pm

For a server job, crack open the manuals to BASIC functions and introduce yourself to the Fmt function.

Mike

reddy · Post by **reddy** » Thu Oct 16, 2008 12:50 pm

Mike,

I really appreciate your response but as i mentioned i already have decimal point in it and decimal point is also a character and hence it is not allowing me to do so and is giving me warnings. So by using FMT function we can only insert decimals when there are no decimals but my case is different. I have a mixture of all kinds of numbers.

please let me know if you have any other options.

reddy · Post by **reddy** » Thu Oct 16, 2008 1:38 pm

Guyz,

Any more suggestions please.

Thank you in advance

chulett · Post by **chulett** » Thu Oct 16, 2008 1:46 pm

Suggest you let us know what your target is. If we're talking sequential file, then in spite of what you wrote the FMT function *is* the answer. For a database or anywhere else where those zeroes are not significant, don't even worry about doing this.

reddy · Post by **reddy** » Thu Oct 16, 2008 1:48 pm

Thank you,

My target is Oracle database.

Is it possible to append zero's in the transformer?

any suggestion?

chulett · Post by **chulett** » Thu Oct 16, 2008 2:01 pm

Sure, but there's no point for a NUMBER field. They aren't significant and Oracle doesn't need them. If you want to see them when you select the data back from Oracle, use an appropriate TO_CHAR() mask but worrying about that during your load is a complete waste of time.

Or is this going into a VARCHAR2 field?

ray.wurlod · Post by **ray.wurlod** » Thu Oct 16, 2008 2:04 pm

In the DataStage BASIC manual you might also investigate the Fix() function.

reddy · Post by **reddy** » Thu Oct 16, 2008 2:19 pm

Thank you again but this is a number field and i think it is not use for a number field but if it is varchar is there any solution?

Please let me know.

FIX() is not solving my problem, thanx though for your time and effort.

chulett · Post by **chulett** » Thu Oct 16, 2008 2:22 pm

You don't have a problem. Seriously, what makes you think you do?

reddy · Post by **reddy** » Thu Oct 16, 2008 2:26 pm

I just had a issue with this this morning and i am unable to figure out how to solve it. so i had to approach you huyz for it.

Suggestions welcome

reddy · Post by **reddy** » Thu Oct 16, 2008 2:28 pm

craig,

can you be more specific of what you mean!!!!

chulett · Post by **chulett** » Thu Oct 16, 2008 2:31 pm

Again - what issue? Did the data not load or otherwise generate an error? Or does it just not look the way you'd like it to when you select it back from the database? If the latter, that's just an issue with your select, not the load. That's the difference I'm trying to see if you understand.