1

I need to create a spatial view with Make Query Layer. My query includes outer joins, so I must combine every table's ID field to create a unique identifier. Since there might not always be a child for every parent record, I use NVL to replace null occurences in the ID fields with a constant value (say 9999).

My problem is that these fields using NVL are not visible in the Unique Identifier Field(s) (optional) list parameter. Any idea why?

Or is there any other method I can use to get unique IDs for my query layer?

I am not allowed to create my view directly in the database.

I use ArcGIS 10.3.1. and the underlying database is Oracle 12c. All ID fields are integers (NUMBER(10, 0)).

5
  • 1
    NUMBER(10) might be mapped to FLOAT64 due to the values exceeding INT_MAX. Use either NUMBER(38) or NUMBER(9)
    – Vince
    Oct 23 '17 at 11:53
  • @Vince What do you mean? The fields are visible in the Unique Identifiers list if I don't use NVL.
    – GISGe
    Oct 23 '17 at 12:06
  • 1
    Did you look at the resulting datatype? If it's not INTEGER NOT NULL, it's not a rowid candidate. Besides, if you force a non-unique value, the result isn't eligible to be a rowid. In the end, you can't simulate a unique ID; either there is one, or you need to restructure the table.
    – Vince
    Oct 23 '17 at 13:33
  • @Vince what do you mean by resulting datatype? The datatype in the query result is the same as the original one. Besides, all table IDs are unique, they are created with a sequence. But due to the fact that I make an outer join, there are sometimes no corresponding records in the joined tables, ie.e null values that I need to replace with something else in ordre for them to be usable in the Unique Identifiers list. The combinations of IDs will always be unique.
    – GISGe
    Oct 23 '17 at 14:26
  • 1
    Nope. That's not what ArcGIS is looking for. The identifier needs to be natural part of the table. Pulling the ID from an outer join will not work. If you think about the purpose of the ID column (to join the graphics and table rows, in either direction), it will be obvious why this will not work. I expect the return type is NUMBER, which is treated as FLOAT64.
    – Vince
    Oct 23 '17 at 14:56