nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Make Query Layer (Data Management) Returns Empty Layer

AJFerrand1

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

Find more posts tagged with

Analysis

Accepted answers

AJFerrand1

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.

All comments

AJFerrand1

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.

AJFerrand1

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.

BB1234

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?

AJFerrand1

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.

Capture.JPG

VinceAngelo

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.