Scientific Notation conversion

Juls · Post by **Juls** » Wed Sep 17, 2008 10:48 am

Hi,
I am loading a csv file to a SQL table and ran into a problem where one of the records has a column that has a decimal value but in scientific notation: 1.7588676455E7
The datatype of the column is decimal(18,2) both in DS and SQl table.
How can I convert this number to a regular decimal that it would go through without error and load to my table?
No, I can't do anything about the file.

Thanks,
Juls

chulett · Post by **chulett** » Wed Sep 17, 2008 1:27 pm

Did you try searching? I got 11 hits for "scientific notation", perhaps one of those would be helpful. Either that or "exponent" should turn something up.

Juls · Post by **Juls** » Wed Sep 17, 2008 3:07 pm

I did both searched but it didn't give me what I need. I did however figure it out on my own.
Thanks.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 17, 2008 3:11 pm

DataStage can handle valid scientific notation numbers provided that the exponent is not more than 38.

chulett · Post by **chulett** » Wed Sep 17, 2008 3:12 pm

If you've figured out something that's not here in the forums, can you please do a couple of things? First would be to mark the thread as Resolved (button at the top of the screen) and secondly post back and let us know what your solution was?

It will be of service to any future searchers with the same problem.

Thanks.

Juls · Post by **Juls** » Wed Sep 17, 2008 3:27 pm

I used the code below in my transform to figure out what the correct value should be and make it precision = 2:

If Num(myField) And Index(myField, "E", 1) = 0 Then OCONV(myField, "MD2P") Else OCONV(FIELD(myField,"E",1)*(10 ^ (FIELD(myField,"E",2)+2)),"MR2")

Hope this helps someone else.