DSXchange

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.

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?

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

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

Regards
Sreeni

[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.

Any Suggestions?

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.

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.

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?

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.

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.

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


Regards
Sreeni

More of a scale than precision issue.

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.

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.