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.

  • 1
    I don't recommend using this procedure. The registered rowid column exists so that the graphics in the field of view and attribute rows in the table can be logically joined. Working back to the shape from a calculated field when an attribute row is highlighted is not likely to be a pleasant task. The id field should be an actual column, with an index.
    – Vince
    Commented Jul 26, 2016 at 19:44
  • Vince, I'm not sure I completely understand your comment, but perhaps more details will help. The table is non-spatial, and won't be used for any spatial purposes. It will just be used to join to another non-spatial table (a linear referencing table) about once a quarter for QC purposes. The table has ~20,000 records. Additionally, there is not a rowed or objected field in the table, so I'm forced to find a workaround. Do you still think this is a bad idea?
    – User1974
    Commented Jul 26, 2016 at 20:12
  • I think I'm starting to see what you mean, Vince. Using more than one column as the unique identifier, especially with a generated field, such as using CAST, is a DISASTER in ArcMap. Unexpected results is an understatement. Depending on the scenario, the CAST fields are being populated with wildly random numbers, such as -240000, 10000, 0, -1848000, 190000. These numbers are supposed to be 4 digit IDS! Frightening.
    – User1974
    Commented Jul 26, 2016 at 20:58
  • 1
    Joining on a computed column is bad juju, but I'm not used to it being that bad. Does the value compute cleanly in SQLDeveloper or TOAD? Please update the question with the extra details.
    – Vince
    Commented Jul 26, 2016 at 21:03
  • The view computes cleanly when brought into MS Access as a linked table. I don't have access to SQLDeveloper or TOAD..
    – User1974
    Commented Jul 29, 2016 at 15:07

2 Answers 2


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.

  • 3
    "NUMBER(38)" would be a better cast target.
    – Vince
    Commented Jul 26, 2016 at 19:46
  • Vince, why is "NUMBER(38)" a better choice?
    – User1974
    Commented Jul 26, 2016 at 20:30
  • 3
    Because "NUMBER(38)" is the type that generates the 32-bit integer that ArcGIS is expecting ("NUMBER" maps to a double)
    – Vince
    Commented Jul 26, 2016 at 21:05

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


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 = '''
cast(ROWNUM  as number(38)) as OBJECTID,
arcpy.MakeQueryLayer_management('path-to-oradb.sde','samples_table', query, 'OBJECTID')


  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.