4

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 Jul 26 '16 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? – Wilson Jul 26 '16 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. – Wilson Jul 26 '16 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 Jul 26 '16 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.. – Wilson Jul 29 '16 at 15:07
3

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.

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

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.

  • I suppose what pateto777 and Vince suggested counts as the RIGHT answer too. Using CAST(MY_DBL_FIELD AS NUMBER(38,0)) does convert a double to an integer, and ArcMap interprets it as such when you drag the view to ArcMap to create a query layer. My problem was related to using multiple fields in a view as the unique identifier for a query layer, which gave me bizarre results. Under normal circumstances, CAST(MY_DBL_FIELD AS NUMBER(38,0)) might work just fine. – Wilson Jan 3 '17 at 12:24
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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