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

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.

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.

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