topic Re: Make Query Layer (Data Management) Returns Empty Layer in ArcGIS Pro Questions

Make Query Layer (Data Management) Returns Empty Layer

AJFerrand1 — Mon, 20 May 2024 19:58:58 GMT

Hello

I am trying to use the tool Make Query Layer (Data Management) but every time it returns an empty layer. I am able to successfully create the query layer by going to Map - Add Data - Query Layer. In both situations, I am using the same SDE database connection, same feature class and table, and same exact query expression.

select a.OBJECTID, a.PIN, a.Subtype, b.parcel_no, b.siteaddress, b.site_citystzip, b.sw_hauler, b.sw_service_day, b.sw_str_sweep_week, a.GlobalID, a.Shape, b.own1 from dbo.parcel_polygon a left outer join dbo.ParcelWeb b on a.pin = b.pin where a.status != 'Retired' and a.status != 'Pending_Retired' and a.status != 'Pending'

Has anyone else had a similar situation or can provide any insight? Also, the reason I am wanting to use Make Query Layer is because I want to include that tool in a Model (eventually python) to run nightly updates and have the data be pushed from the resulted query layer to an SDE feature class.

Thank You

Re: Make Query Layer (Data Management) Returns Empty Layer

VinceAngelo — Mon, 20 May 2024 20:29:09 GMT

Your SQL code would be far more legible if you formatted it in a SQL code block:

SELECT a.OBJECTID, a.PIN, a.Subtype, b.parcel_no, b.siteaddress, b.site_citystzip, b.sw_hauler, b.sw_service_day, b.sw_str_sweep_week, a.GlobalID, a.Shape, b.own1 FROM dbo.parcel_polygon a LEFT OUTER JOIN dbo.ParcelWeb b ON a.pin = b.pin WHERE a.status != 'Retired' and a.status != 'Pending_Retired' and a.status != 'Pending'

From this vantage point, we can see:

You've used "!=" not the ISO standard SQL "not equal" operator "<>"
Your WHERE clause is an AND of several NOT EQUAL tests (which can't be effectively searched by as index)

So the first question is what are the valid values in a.status?

SELECT DISTINCT a.status FROM dbo.parcel_polygon a

And what are the table counts across the joins?

SELECT (SELECT count(*) FROM dbo.parcel_polygon ) as acount, (SELECT count(*) FROM dbo.ParcelWeb ) as bcount, (SELECT count(*) FROM dbo.parcel_polygon LEFT OUTER JOIN dbo.ParcelWeb b ON b.pin = a.pin) as jcount, (SELECT count(*) FROM dbo.parcel_polygon a WHERE field not in ('Retired','Pending_Retired','Pending')) as qcount

Debugging joins is a stepwise process, stripping out not required components to determine where your logic doesn't align with that of the database.

- V

Re: Make Query Layer (Data Management) Returns Empty Layer

AJFerrand1 — Mon, 20 May 2024 20:41:07 GMT

Vince,

Thank you. I do understand how it should be formatted in SQL however as mentioned I am working in the tool Make Query Layer and am forced to use window (as shown on the attachment) and do not have the freedom of doing formatting like you showed.

As mentioned in my post above, my issue is the Query Layer is not populating data when using the tool Make Query Layer. But if I use the same exact expression (regardless of formatting) in the window New Query Layer (which is accessed by from Map - Add Data - Query Layer), the Query Layer gets created without any problems.

Also when I run Make Query Layer, it returns an empty layer. However if I right click on the newly created query layer and go to Properties then Source then click on the pencil icon to edit the query, I am prompted with the same window used in New Query Layer (as mentioned above). If I complete the prompts from that window (Validate, Next, Finish) the layer is now populated with data. Even though I made zero changes to the expression.

Re: Make Query Layer (Data Management) Returns Empty Layer

Bud — Tue, 21 May 2024 21:08:39 GMT

Are the tables registered with an enterprise geodatabase?

I ask because I had a somewhat similar problem in an Esri Supoport case with FCs that weren't registered with a geodatabase (ArcGIS Pro 3.3.0; Oracle 18c database; non-geodatabase):

Esri Case #03625510 - M-enabled SDO_GEOMETRY FC not displaying in map [unregistered feature class]
BUG-000163909: 3D data in an unregistered feature class with SDO_Geometry is not displayed on the map in ArcGIS Pro.

The layer didn’t display in the map, although there were rows in the attribute table. But if I made any change to the default query layer SQL, then that fixed the query layer; it would display in the map. Even putting a random comment like /* hot dog! */ in the SQL makes the layer work.

What version of SQL Server?
What version of ArcGIS Enterprise?
What version of Pro?
Is your unique ID column truly unique and without nulls?

Re: Make Query Layer (Data Management) Returns Empty Layer

AJFerrand1 — Tue, 21 May 2024 14:06:53 GMT

Hello Bud,

Yes the parcel_polygon feature class is registered as versioned (which we tried adding that table as parcel_polygon_evw, since it adds _evw when the table is registered as versioned but still no luck).

Exactly! I would just edit the query by adding a space or nothing at all and it would work. But the tool itself isn't creating the layer.

What version of SQL Server? We are using 2022.
What version of ArcGIS Enterprise? We are at 10.9.1
What version of Pro? We are using 3.1.5, which I know Esri frowns upon. So I have another machine on 2.9.5, but haven't test it there yet.
Is your unique ID column truly unique and without nulls? Yes.

Re: Make Query Layer (Data Management) Returns Empty Layer

Bud — Tue, 21 May 2024 14:19:27 GMT

Just to clarify, registered with the geodatabase is different from registered as versioned. But I assume it's not possible to register as versioned without first being registered with the geodatabase. So I think you answered the registered as versioned question indirectly.

Re: Make Query Layer (Data Management) Returns Empty Layer

Bud — Tue, 21 May 2024 14:57:27 GMT

Is the relationship between dbo.parcel_polygon and dbo.ParcelWeb one-to-many?

If yes, then that suggests to me that you wouldn't have a unique ID column to work with in the query, due to the LEFT OUTER JOIN. Unless you're using multiple fields as the unique ID in the query layer, which I haven't done; I always use a single field as the unique ID, out of habit.

I often generate a unique ID column in the query using Oracle's rownum pseduocolumn:

select
    cast(rownum as int) as unique_id,
    a.*
from
    (select ...) a

Does SQL Server have an equivalent mechanism to generate unique IDs in a query?

Just a heads up that you likely shouldn't check for duplicate IDs in ArcGIS Pro/in the query layer, since query layers automatically exclude duplicate rows from the resultset. I think you should check for duplicate IDs (in the query, not in the underlying tables) using a query in a SQL client.

There are portable SQL clients (no Windows admin privileges needed).

Does a query layer work if you simplify it? For example, SELECT * FROM DBO.PARCEL_POLYGON .

Re: Make Query Layer (Data Management) Returns Empty Layer

George_Thompson — Tue, 21 May 2024 14:38:37 GMT

If the data is versioned, you would have to use the versioned view to query data in the DEFAULT version.

I am not sure that you can change the version in the query layer window. This could be done in SQL Server as a view.

You can reconcile / post all the edits to default, then compress and it should move the edits to the base table.

Re: Make Query Layer (Data Management) Returns Empty Layer

AJFerrand1 — Tue, 21 May 2024 16:07:00 GMT

This whole time I have been trying to run Make Query Layer in ArcGIS Pro 3.1.

I just ran the same tool and same expression in ArcGIS Pro 2.9, and the tool worked perfectly fine.

It appears there is a compatibility issue between the version of Pro I was using and the version of Enterprise.