I created a query layer in ArcGIS Pro from a feature hosted in our SDE that looks like so: select distinct parcel_id , primary_address , owner , shape from parcel . parcels_master_mv where ( UPPER ( owner ) LIKE 'MANATEE COUNTY%' OR owner LIKE 'MANATEE, COUNTY%' OR owner LIKE 'COUNTY OF MANATEE%' OR owner LIKE '%CEMETERY%' OR owner LIKE '%CEMATERY%' OR owner LIKE 'SIXA INV%' OR owner LIKE 'TIITF%' ) AND ( LUC IN ( '7600' , '8086' , '8600' , '9400' , '9500' ) ) AND ( UPPER ( owner ) NOT LIKE '%MOSQUITO%' AND UPPER ( owner ) NOT LIKE '%PORT%' AND UPPER ( owner ) NOT LIKE '%AUTHORITY%' AND UPPER ( owner ) NOT LIKE '%HOUSING%' AND UPPER ( owner ) NOT LIKE '%HABITAT%' AND UPPER ( owner ) NOT LIKE '%AUDOBON%' AND UPPER ( owner ) NOT LIKE '%BANK%' AND UPPER ( owner ) NOT LIKE '%CHURCH%' AND UPPER ( owner ) NOT LIKE '%FAMILY%' AND UPPER ( owner ) NOT LIKE '%FARM%' ) order by parcel_id desc Why I believe there's a bug is because when I open the attribute table and start scrolling, I get an error a couple rows in that says "Failed to retrieve a page of rows." After I select okay, the attribute table crashes - and it does so at the same point every time I reopen the table. When I remove the "order by" command, I can scroll freely though the attribute table without error. It seems to be very similar to this bug that was reported resolved with Pro 2.3: What does "Failed to retrieve a page of rows" imply?

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; } } }

Possible Bug with Query Layer Attribute Table in Pro?

Anonymous User

I created a query layer in ArcGIS Pro from a feature hosted in our SDE that looks like so:

<SPAN class="keyword token">select</SPAN> <SPAN class="keyword token">distinct</SPAN> parcel_id<SPAN class="punctuation token">,</SPAN> primary_address<SPAN class="punctuation token">,</SPAN>owner<SPAN class="punctuation token">,</SPAN> shape <SPAN class="keyword token">from</SPAN> parcel<SPAN class="punctuation token">.</SPAN>parcels_master_mv <SPAN class="keyword token">where</SPAN> <SPAN class="punctuation token">(</SPAN>UPPER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">LIKE</SPAN> 
<SPAN class="string token">'MANATEE COUNTY%'</SPAN>
 <SPAN class="operator token">OR</SPAN> owner <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'MANATEE, COUNTY%'</SPAN>
 <SPAN class="operator token">OR</SPAN> owner <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'COUNTY OF MANATEE%'</SPAN>
 <SPAN class="operator token">OR</SPAN> owner <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%CEMETERY%'</SPAN>
 <SPAN class="operator token">OR</SPAN> owner <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%CEMATERY%'</SPAN>
 <SPAN class="operator token">OR</SPAN> owner <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'SIXA INV%'</SPAN>
 <SPAN class="operator token">OR</SPAN> owner <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'TIITF%'</SPAN> <SPAN class="punctuation token">)</SPAN>
 <SPAN class="operator token">AND</SPAN> <SPAN class="punctuation token">(</SPAN>LUC <SPAN class="operator token">IN</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'7600'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'8086'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'8600'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'9400'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'9500'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
 <SPAN class="operator token">AND</SPAN>
 <SPAN class="punctuation token">(</SPAN> UPPER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%MOSQUITO%'</SPAN>
 <SPAN class="operator token">AND</SPAN> UPPER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%PORT%'</SPAN>
 <SPAN class="operator token">AND</SPAN> UPPER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%AUTHORITY%'</SPAN>
 <SPAN class="operator token">AND</SPAN> UPPER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%HOUSING%'</SPAN>
 <SPAN class="operator token">AND</SPAN> UPPER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%HABITAT%'</SPAN>
 <SPAN class="operator token">AND</SPAN> UPPER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%AUDOBON%'</SPAN>
 <SPAN class="operator token">AND</SPAN> UPPER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%BANK%'</SPAN>
 <SPAN class="operator token">AND</SPAN> UPPER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%CHURCH%'</SPAN>
 <SPAN class="operator token">AND</SPAN> UPPER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%FAMILY%'</SPAN>
 <SPAN class="operator token">AND</SPAN> UPPER<SPAN class="punctuation token">(</SPAN>owner<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'%FARM%'</SPAN><SPAN class="punctuation token">)</SPAN>
 <SPAN class="keyword token">order</SPAN> <SPAN class="keyword token">by</SPAN> parcel_id <SPAN class="keyword token">desc</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Why I believe there's a bug is because when I open the attribute table and start scrolling, I get an error a couple rows in that says "Failed to retrieve a page of rows." After I select okay, the attribute table crashes - and it does so at the same point every time I reopen the table.

When I remove the "order by" command, I can scroll freely though the attribute table without error.

It seems to be very similar to this bug that was reported resolved with Pro 2.3: What does "Failed to retrieve a page of rows" imply?‌

Trouver plus de publications taguées avec

Accepted answers

bixb0012

My guess is that it is tied to your use of DISTINCT and ORDER BY. Since query layers are dynamic, the SQL you use to define the layer is constantly being called to retrieve records. Mixing DISTINCT and ORDER BY is messy because both involve sorting, but typically each sorts using a different mechanism. My guess is that the records being returned by the query are changing during one of the subsequent calls of the SQL statement, and that having different records returned is causing an issue with some kind of internal processing by ArcGIS.

I recommend you read How SQL DISTINCT and ORDER BY are Related – Java, SQL and jOOQ. and consider restructuring your SQL.

All comments

YungKaiChin

If all else fails, try create a new id field and populate with row_number().

Robert LeClair

Lea - in some of the Support Services cases I've been reviewing it mentions the issue was resolved "by changing query to exclude duplicates on the unique id, enhancement request also logged. ENH-000119340: Customer requests behavior in Pro be changed to be the same as ArcMap when adding a query layer with duplicate OID rows."

bixb0012

I recommend you read How SQL DISTINCT and ORDER BY are Related – Java, SQL and jOOQ. and consider restructuring your SQL.

DanPatterson_Retired

Lea, don't use query layers, but it first Note in this link Choose a unique identifier field—Query layers | ArcGIS Desktop might be an issue so I don't understand how distinct would handle this

Anonymous User

Hi Dan - I've defined the primary key as parcel_id (which does have duplicate values in the original dataset), but I made sure to call distinct values in the query.

Could Pro somehow be picking up on duplicates in the background when the query runs?

DanPatterson_Retired

Lea, did you follow the related and buried thread?

https://community.esri.com/thread/222727-error-opening-table-in-pro-failed-to-retrieve-a-page-of-rows it has more recent posts

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

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