Data Insertion Issue with Float datatype

sohasaid · Post by **sohasaid** » Wed Mar 03, 2010 7:35 am

Dears,

I have a problem loading values with 'float' data types into the oracle 10G database, although the DataStage can read it right, but it loads it in a wrong way as follows:

Source Column Data type: Real (7,0)
Source Value: 5555.49
Oracle Ent. Stage Viewer: 5555.49
Destination Column data type: Float (63)
Loaded Value in Destination : 5555.49020000000019

Note: DataStage reads the Real datatype as Double.

I tried to convert this column to char using to_char function at the insert statement after consulting an oracle dba but it didn't work.

- Oracle database Destination: 10G (Oracle Enterprise Stage)
- Source database: Oracle RDB V7.1 (ODBC Enterprise Stage)

I don't know why Oracle 10g adds these digits?

Regards.

chulett · Post by **chulett** » Wed Mar 03, 2010 7:55 am

That's the nature of floating point. Are you doing any transforms or a simple move? Any chance your source always has a scale of 2?

sohasaid · Post by **sohasaid** » Wed Mar 03, 2010 8:14 am

It's a simple move and the source's scale sometimes is more than 2.

Sreenivasulu · Post by **Sreenivasulu** » Wed Mar 03, 2010 10:09 am

If you want the exact precision as source the store as 'varchar'. This is done for some 'critical' fields

Regards
Sreeni

sohasaid · Post by **sohasaid** » Wed Mar 03, 2010 4:20 pm

[quote="Sreenivasulu"]If you want the exact precision as source the store as 'varchar'. This is done for some 'critical' fields

U mean changing the column's datatype into VARCHAR instead of FLOAT? and what do you mean by 'critical'?

Regards.

sohasaid · Post by **sohasaid** » Sun Mar 07, 2010 4:12 am

Any Suggestions?

chulett · Post by **chulett** » Sun Mar 07, 2010 7:27 am

Contact your official support provider. Me, I would never use a FLOAT for something like this precisely because of the issues that you are seeing that they can cause.

sohasaid · Post by **sohasaid** » Sun Mar 07, 2010 8:58 am

Okay, I will contact them. But if I need to get rid of the FLOAT datatype, what datatype you recommend to be the replacement without losing any data?

Thanks for the reply.

chulett · Post by **chulett** » Sun Mar 07, 2010 9:44 am

What kind of data are you storing? You said the scale can be "more than 2"... how much more? How about a NUMBER(x,y) with appropriate X and Y values?

sohasaid · Post by **sohasaid** » Sun Mar 07, 2010 10:08 am

chulett wrote:How about a NUMBER(x,y) with appropriate X and Y values?

The maximum scale I currently have is 6. Converting REAL to NUMBER(x,y) seems to be a good idea but my main concern if the scale of the upcoming data in the future exceeds the identified scale even it was 10 which is the maximum scale I can assign to a NUMBER. No guarantee to transfer data safely and a possible data truncation could happen, Right?

Regards.

chulett · Post by **chulett** » Sun Mar 07, 2010 10:41 am

Possible, sure. As I said, it really depends on the nature of your data and if a FLOAT/REAL is the appropriate way to store it (something you would have to tell us) then you'll need to find some way to deal with it. I'm not aware of any 'tricks' to keep those floating point issues from biting you in the butt on occasion.

In your shoes I would open a case with your official support provider and see what they suggest. Also curious if you've been through the Parallel Job Developer's Guide discussions on the 'Float type' and the 'In_format' options it supports or tried any of the 'DFloat' type conversion functions? Maybe something there would help.

Sreenivasulu · Post by **Sreenivasulu** » Sun Mar 07, 2010 11:29 am

You can use 'decimal' instead of 'float' with the required precision.

Regards
Sreeni

chulett · Post by **chulett** » Sun Mar 07, 2010 11:42 am

More of a scale than precision issue.

sohasaid · Post by **sohasaid** » Wed Mar 10, 2010 9:39 am

I found out an important fact here that DataStage has no problem with REAL, DOUBLE, OR FLOAT datatypes. DataStage transfers data as it stored as the source even it was REAL datatype. I realized that the main issue when inserting REAL data into Oracle database that it could be inserted in a wrong way.
For example, if I inserted the value '5502.57' using statement into Oracle RDB (my data source), it's inserted as '5502.5703' into the database. But DataStage moves it as '5502.5703' which is correct.

But the thing I can't understand; when trying to view the float data from ODBC Enterprise stage, it shows as '5502.57' which is not correct although it's transferring it as '5502.5703'?

My issue is solved now but I don't know why DataStage view float wrongly although it transfers it correctly?

P.S.Thanks guys for your help especially chulett.

chulett · Post by **chulett** » Wed Mar 10, 2010 10:37 am

Curious what you see when you view the data outside of DataStage, say with a tool like Toad or just sqlplus? I would put very little stock in what the View Data option from the stage shows you, it's not always gospel as the viewer has issues of its own. Use it as a general check of correctness but when you really really need to know what's in there, use another tool.