I have a field in a table that is a double data type, but the values are actually integers. The table is from third-party software, so I don't have control of the field data types.
I'm attempting to make a database view of the table, and add it to ArcMap (which I believe makes it into a query layer). Arcmap requires a unique identifier field be selected when the view is added to the map. There isn't a single unique field in the table, but the combination of two other fields plus the double field is unique. I plan to select all three fields as the unique id (sort of like a composite key). The problem is that it that ArcMap seemingly does not recognize double fields as unique identifiers (and probably for good reason). Text fields and integers seem to be recognized just fine.
As a workaround, I'm trying to convert the double field to an integer field in the database view. I've tried various combinations of the TO_NUMBER, ROUND, FLOOR, TRUNCATE, and CAST Oracle functions, with no success.
How can I get the table into ArcMap as a database view, and use the double field as a unique identifier?
Environment: Oracle 11g, SDE 10.3.1, ArcGIS Desktop 10.3.1
Update Jul-29-2016:
The table is non-spatial. It will just be used to join to another non-spatial table about once per quarter for QC purposes. The table has ~20,000 records. There isn't a ROWID or OBJECTID field in the table, thus forcing me to find a workaround.
Best Answer
You have to create the view using a select statement with a casting case. For instance: SELECT CAST (miles AS INT) Once your view is created, it should appear in the catalog of your database connection.