ArcGIS Enterprise 10.7.1 and ArcGIS Pro 2.9 - LEFT JOIN in database views?

Question

Hi,

I regularly use Oracle-based database views built from scratch from other tables with ST_GEOMETRY, registered in a 10.6.1 Entreprise GDB. These views performs well in third-party SQL client in which they execute in about 1sec...

However, I noticed that polyline views do not display entirely in ArcGIS Pro. I tweak my SQL views a lot but with no luck. The attribute table do open though and show all records.

In addition, this particular view uses LEFT JOIN syntax. Is there a know limitation in database view in ArcGIS Pro and/or Enterprise GDB? If I build a similar view with regular joins on primary and foreign keys in a WHERE clause, it does displays the polyline. This let me wonder if LEFT JOIN is not digested with the ArcGIS platform... And/or is there any settings and/ot tuning I can tweak? (such increasing response time when ArcGIS Enterprise execute the view in order to receive the geometry)

For info, my polyline is initially 65'000 records in WGS84 and I also reduce it to 340 records and same issue appears!

Any feedback would be appreciated

Scott_Tansley · Answer

In all honesty, I think you may get better results to your question if you targetted the Geodatabase community, rather than the ArcGIS Enterprise community.  Ask your question here:

https://community.esri.com/t5/data-management/ct-p/data-management

I don't spend enough time inside databases to help, but I've seen people do similar things without realising that the OID must be unique.  Yes, it draws, no you can't do analysis/view tables like you say.  Move or reask your question, and you'll probably get a better response?

VincentLaunstorfer · Answer

Thanks. Fortunately, my SQL-client does not allow to create duplicate column names in a view... Although, this is an important point, if there is any duplicate OBJECTID, the layer usually draws but it is not possible to open the attribute table.

To come back to my initial issue, I forgot to mention that if I create a table (not a view) with same SQL query, it does work and display correctly in ArcGIS Pro. As I said, my SQL has 2 LEFT JOIN and a nested decode (about 10) statment in SELECT to fetch data in the LEFT JOIN tables... Probably too heavy for ArcGIS Pro to execute the query and receive results to display... If I create the same views without the nested decode, the layer displays but slowly.

To me, it is like ArcGIS Pro not 'patient' enought to receive results to display.

I have to admit I am stuck with complex queries. I will have to create tables on a regular basis in the scheduler and create views on them.

Scott_Tansley · Answer

Is there a possibility that the join is duplicating an ObjectID or the numeric ID that you use instead of an OID?  If ArcGIS is parsing data, and finds a duplicate primary ID then it will stop loading/drawing data.  It's a rule of all feature classes that the OID is unique.  Just something to rule out.