Converting double field to integer in database view so ArcMap query layer can use field as unique identifier

Question

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.

Answer 1

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.

Answer 2

Some points to add to the other answers that I learned through trial and error.

If the Oracle table doesn't have a unique id field use ROWNUM psudocolumn. (Note: be aware that features might get a different number each time the query is called.)

cast(ROWNUM as number(38))

In ArcGIS Pro I have to explicitly name the cast table, not sure if this applies to ArcMap also. Without the name the query runs successfully but when trying to open the table there is an OID column has null value error.

-- Use auto generated row number and rename
cast (ROWNUM  as number(38)) as OBJECTID
-- Use an existing unique id field and keep the same name
cast (PLOT_ID as number(38)) as PLOT_ID

Wildcard field selection won't work with cast:

select cast(…) as ID, * from DB.TABLE

Result:

arcgisscripting.ExecuteError: Failed to execute. Parameters are not valid.
WARNING 001005: The output already exists.
ERROR 000358: Underlying DBMS error[ORA-00936: missing expression]
Failed to execute (MakeQueryLayer).

Which makes sense after thinking about it, because how would you stop the recursion? The ID field would be included again with the * selection.

Full python example:

import arcpy
query = '''
select 
cast(ROWNUM  as number(38)) as OBJECTID,
cast(SAMPLE_ID AS NUMBER(38)) as SAMPLE_ID, 
LOCATION, UTM_EAST, UTM_NORTH, UTM_ZONE 
from ORADB.SAMPLE_LOCATIONS
'''
arcpy.MakeQueryLayer_management('path-to-oradb.sde','samples_table', query, 'OBJECTID')

Sources:

• Number(38) courtesy of @Vince, in this thread
• Rownum idea courtesy of Ryan Monk and @Wilson in this very question, which I totally missed on first couple readings

Answer 3

What worked:

The solution seems to be to use CAST(MY_DBL_FIELD AS NUMBER(9,0)). This converts the double field to an integer (ArcMap recognizes the field as a long integer).

This allowed me to drag the view to ArcMap, which makes a query layer, and select multiple fields to use as a unique identifier (including the double field that I converted to an integer).

I figured out the 9,0 (precision,scale) thing by referring to: Conversion of ORACLE Data Types to Microsoft Access. Yes, this is an MS Access document, and not an ArcMap document, but it seems to work for me.

For the record, I think a better/cleaner/less risky way of getting a unique ID from a table that doesn't have a single unique identifier field, is to use a Oracle ROWNUM Pseudocolumn. And yes, ArcMap recognizes the field as a double, which can't be used as a unique identifier in a query layer, so I had to convert it to an integer too: CAST(ROWNUM AS NUMBER(9,0)).

What didn't work:

Dragging a view into ArcMap that had a calculated field using the formats below resulted in mayhem in the query layer:

• CAST(MY_DBL_FIELD AS NUMBER(38))
• CAST(MY_DBL_FIELD AS NUMBER(38,0))
• CAST(MY_DBL_FIELD AS INTEGER)

I kid you not; when the above was used, the values in any calculated fields, such as the double field that was converted to a integer, were replaced by the value from an unrelated COST field, or set to 0, depending on the scenario. Example: MY_DBL_FIELD still has the column name MY_DBL_FIELD in the attribute table, but it now shows totally bogus values, which were grabbed from another field. Insane!

What sort-of worked, but I wouldn't trust it:

As mentioned, dragging views into ArcMap that had the CAST precision,scale listed above, resulted in Mayhem. But using the exact same SQL that was used for the view, but instead making a Query Layer in ArcMap from scratch worked just fine. No mayhem. The fact that a query layer from a view caused mayhem, but a query layer from scratch worked fine, drove me a bit nuts, but it does work in a pinch.