Comparing Access Number Double field vs. Oracle VARCHAR field

[TerryHogarth21]

Hi Folks

Has anyone ever had experience with trying to compare a linked table that is in Access (connecting to an ORACLE database) to a local table in Access?

I have an access table - let's call it table1 and an ORACLE table called table2.

There is two fields in each table, Field1 and Field2.

Field1 are number and Field2 is a number both double field size.

The ORACLE table Field1 is number data type and Field2 is VARCHAR data type.

When I join Field1 to Field1 from both tables, it works and I get the results I'm looking for.

However, I notice that in the SQL string if I add a where clause similar to the below that calculations seem off.

 WHERE (table1.Field2 - table2.Field2) <> 0;

I'm noticing some odd behaviors since the field types are different.

Even when I import the table locally into Access from Oracle, I still get these odd variances of the precision such as .0000000000000001235

ORACLE's tables can't be adjusted - though I am wondering is there anything I can do in Access to alleviate the issue?

 

[strive4peace]

> "Field1 are number and Field2 is a number both double field size ... When I join Field1 to Field1 from both tables, it works and I get the results I'm looking for."

Double-precision is good for big numbers BUT IS NOT AN ACCURATE DATA TYPE FOR EXACT COMPARISONS. For joining reliably, you cannot use single or double precision numbers. Can your values be converted to Long Integer data type?

 

[TerryHogarth21]

> "Field1 are number and Field2 is a number both double field size ... When I join Field1 to Field1 from both tables, it works and I get the results I'm looking for."

Double-precision is good for big numbers BUT IS NOT AN ACCURATE DATA TYPE FOR EXACT COMPARISONS. For joining reliably, you cannot use single or double precision numbers. Can your values be converted to Long Integer data type?

I did a quick test and changed the values to Long Integer data type, closed and reopened but still no luck in getting what I was hoping for.
I've done the same thing before in SQL Server with Access (both Microsoft products) and it works fine due to the data types being the same to run SQL logic via mathematical calculations but talking to other developers here, they always find odd data working with VARCHAR in Oracle when trying to compare with a Microsoft Application.

The precision isn't exact but from this standpoint it may need to just be truncated before comparing since we cannot change the ORACLE tables.

 

[strive4peace]

the other thing to keep in mind is that data types need to be the same for linking -- and SIZE should be the same as well. You can, however, use criteria to join information. In those cases, however, you lose the ability to do Left and Right joins.