Create FGDB database view using the ORDER BY clause: ERROR 999999

Bud — Thu, 18 Jan 2024 20:53:20 GMT

ArcGIS Pro 3.2.1; file geodatabase.

Background:

In a mobile geodatabase, I can create the following database view without issue:

select
    population_centre,
    population_2021
from
    cities
order by
    population_2021 desc

But if I try to create the same view in a file geodatabase, then I get ERROR 999999:

The culprit seems to be the ORDER BY clause. If I were to remove the ORDER BY, then I could create the view.

select
    population_centre,
    population_2021
from
    cities

But that would defeat the purpose of the view: to sort population_2021 (descending).

I'm aware that there is a Sort geoprocessing tool. But I don't want to use it since it outputs a static FC. I want a dynamic query.

Related:

Esri Case #03531064 - Create FGDB database view using the ORDER BY clause: ERROR 999999
...this is a known defect that will sadly not be addressed at this time. The 'ORDER BY' parameter must not be used as a part of a view as it is not supported on all views in many data sources. This applies to a file geodatabase and many RDBMS, for example, Oracle.
BUG-000133228 The Create Database View (Data Management) tool fails when a derived field is used in the ORDER BY clause.
BUG-000134186 The 'Create Database View' tool fails to create a new view, and returns an error message, "Error 999999: Something unexpected caused the tool to fail." when the 'ORDER BY' parameter is used for a file geodatabase in ArcGIS Pro.

Question:

Are there any workarounds for this issue?

Re: Create FGDB database view using the ORDER BY clause: ERROR 999999

Bud — Thu, 18 Jan 2024 20:37:00 GMT

I can wrap it in an outer query:

select
    *
from
    (
    select
        population_centre,
        population_2021
    from
        cities
    order by
        population_2021 desc
    )

Now, I can create the view without issue. The ORDER BY works as expected.

topic Create FGDB database view using the ORDER BY clause: ERROR 999999 in ArcGIS Pro Questions

Create FGDB database view using the ORDER BY clause: ERROR 999999

Re: Create FGDB database view using the ORDER BY clause: ERROR 999999