Data Insertion Issue with Float datatype
Moderators: chulett, rschirm, roy
Data Insertion Issue with Float datatype
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.
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.
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
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?chulett wrote:How about a NUMBER(x,y) with appropriate X and Y values?
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers