Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

How do I copy data from one FLOAT column in Table A to another in Table B without loss of data precision?

Posted on 2008-10-14
7
Medium Priority
?
617 Views
Last Modified: 2013-12-19
Using Oracle 9.
In the same database, I wish to copy the values of Column A  in Table 1 that is formatted as FLOAT (22) into Column A of Table 2 that is also formatted as FLOAT (22). But when I try to do so, I get a loss in precision of the value.

For example, a value of 7393.80896668582 ends up as a value of 7393.809 ???

Tried several ways to do this.
1. Straight INSERT INTO Table 2 (Col A) select (Col A) from Table 2
2. Run query to extract rows from Table 1..... use TO_CHAR (Col A) ..... this correctly gives the right precision in the dumped csv or xls file..... then load data into Table 2.... same loss of precision...

Browsing for answers, this is what I'm concluding:

1. Something to do with the Oracle ODBC driver not handling things correctly? (Oracle ODBC driver 9.02.00.65). [And, by the way, PC is running Windows 2000 SP4]

2. That I need to jump through many more hoops, eg: CAST the orignal FLOAT value as VARCHAR, then CAST the VARCHAR as a NUMBER.... (and here I'm thinking NUMBER FLOATING).... then try again.... my problem here is that I'm not sure of the syntax.... eg NUMBER (x, y) ... what should the x,y values be? What is the syntax for NUMBER...FLOATING? And, anyway, is this valid ? will this work ?

3. That I'm a total idiot

Hopefully the answer is 3, because I cannot believe there is not a simple way to do this?

And it's urgent, coz I've gotta rescue data on a live system....

Appreciate any help here.

Rgds
Jack
0
Comment
Question by:quaylochjack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 22711688
can you please try without the () on the select side?



>1. Something to do with the Oracle ODBC driver not handling things correctly? (Oracle ODBC driver 9.02.00.65). [And, by the way, PC is running Windows 2000 SP4]
no, as the SQL INSERT ... SELECT will not bring the data to the client, actually, it will be entirely handled by the server engine

2. we will see later, if below suggestion does not work

3. I don't think so :)

1. Straight INSERT INTO Table 2 (Col A) select Col_A from Table 2

Open in new window

0
 

Author Comment

by:quaylochjack
ID: 22711899
angelll..... thanks.. but I was just using short-hand to describe the issue.... I did use the correct syntax...

Insert into Table 1
       (Col A)
Select
       Col A
from Table 2
0
 

Author Comment

by:quaylochjack
ID: 22711953
So just to be clear...... using the correct INSERT INTO syntax did not work.... I lost precision....
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 22711979
what is the exact version of the oracle server, please?
also, can you show the DESCRIBE of the 2 tables?
finally, what about:
INSERT INTO Table 2 (Col A) select cast(cast(Col_A as number(26,20)) as float) from Table 2

Open in new window

0
 

Author Comment

by:quaylochjack
ID: 22712186
angellll: tried
INSERT INTO Table 2 (Col A) select cast(cast(Col_A as number(26,20)) as float) from Table 2
 Still same problem --> only 3 decimal places.....

Oracle database version : 09.02.0080

Describe? What do you need here?

0
 

Accepted Solution

by:
quaylochjack earned 0 total points
ID: 22719418
Ok...managed to solve my problem..... here's the story..

I needed to create a lookup table. In doing so, I simply looked at the properties / data types of columns in existing "live" tables in the db to specify my CREATE TABLE sql. So, for the FLOAT columns I saw that these were FLOAT (22) and duly I added a line in my CREATE TABLE script:
                        CREATE TABLE TABLE 1
                        ( Column 1 NVARCHAR 2 (60),
                          Column 2 CHAR (10),
                          <....>,
                         Column x FLOAT (22),
                          < etc>)
When I did this, I got the problem with trying to copy (INSERT INTO) the table. The "float data" that I loaded lost precision.
So, I modified the CREATE TABLE script, and this time did not specify a precision for the FLOAT column - in other words, just let Oracle handle it:
                         Column x FLOAT,
                         <etc>)
Now when I looked at the table properties after running the CREATE TABLE sql, sure enough Oracle "did" handle it - and showed the data type to be FLOAT (22)..... by default?
Now when I ran my straight forward INSERT INTO script, sure enough the "float data" came across fine..
[And note, it was the simple INSERT INTO script... I did not need to CAST anything to anything].

So.... problem sorted......although I'm not sure I understand what Oracle does here.......

Meanwhile thank you for your help anyway....

Rgds
Jack
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22719608
coool!

>.although I'm not sure I understand what Oracle does here.......
what interface did you use to see the FLOAT(22), actually?
I assume the problem lies there...

CHeers
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

647 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question