ORACLE digital equivalent comparison

The ORACLE digital equivalent comparison imports data to the Oracle EBS system through the interface table. After the system is imported, it is necessary to verify that the data in the system is consistent with that in the Custom interface table. The main check field is the material cost ITEM_COST, so the following problem occurs: -- check the imported material cost and the customer's material cost SELECT cci. item_number, cci. item_cost FROM cux_cost_import cci where not exists (SELECT 1 www.2cto.com FROM mtl_system_items_ B msib, cst_pac_item_costs cpic WHERE msib. inventory_item_id = cpic. inventory_item_id AND msib. organization_id = 1811 AND cpic. pac_period_id = 174014 AND msib. segment1 = cci. item_number -- AND to_char (cci. item_cost) = to_char (cpic. item_cost) AND cci. item_cost = cpic. item_cost) AND cci. item_number not in (10010010003,100 10030040001,100,) group by cci. item_number, cci. item_cost; five matching data entries appear: (the imported data is different)

The result of searching the custom table is www.2cto.com select distinct cci. item_number, cci. item_cost FROM cux_cost_import cci WHERE 1 = 1 AND cci. item_number IN ('123', '123', '123', '123', '123 ');
Query the data in the system. The result is as follows: SELECT msib. segment1, cpic. item_cost --, to_char (cpic. item_cost) FROM mtl_system_items_ B msib, cst_pac_item_costs cpic WHERE msib. inventory_item_id = cpic. inventory_item_id AND msib. organization_id = 1811 AND cpic. pac_period_id = 174014 www.2cto.com AND msib. segment1 IN ('20170301', '20160301', '20160301', '20160301 ');
Why are they all the same? In an instant, I have a full face... I thought it was a problem with SQL. Should I check it again? After a long time, I can see that I remember that when I was writing C and C ++ in a university, I would not use an equal sign for the comparison of numbers. I usually write abs (l_temp-l_compare_number) like this) <10-10 think of this, and you will know the reason (detailed explanation below). When the number is matched, to_char is used to replace the number with a string. The result is as follows: there is another difference after to_char. Why is there another piece of data that is not scientific?

Why is there another piece of data that is not scientific? Then, I pulled out the custom interface table and the data in the system (for example), and looked at to_char. Let's go. This ...... I'm speechless. Custom table data: select distinct cci. item_number, to_char (cci. item_cost) FROM cux_cost_import cci WHERE 1 = 1 www.2cto.com AND cci. item_number IN ('123', '123', '123', '123', '123'); System Data: SELECT msib. segment1, cpic. item_cost, to_char (cpic. item_cost) FROM mtl_system_items_ B msib, cst_pac_item_costs cpic WHERE msib. inventory_item_id = cpic. inventory_item_id AND msib. Organization_id = 1811 AND cpic. pac_period_id = 174014 AND msib. segment1 IN ('000000', '000000', '000000', '000000', '000000'); www.2cto.com concluded that the storage of numbers IN computers is accurate, therefore, we cannot store all rational numbers. The specific precision is determined by the computer hardware, operating system, and compiler. For example, if the progress of a computer is 10-3 or 0.001, how can we store the precision data such as 1.12345? In this progress, 1.12345 may be 1.123 or 1.124, so the comparison of data beyond the precision is meaningless. Oracle storage data also has a problem similar to the above, which is why there is a different piece of data after to_char, Oracle SQL will first perform a round on the number when matching, it seems that 10 is a decimal number. Therefore, pay attention to this issue when writing SQL statements. Author: coder. zhw