topic Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)? in ArcGIS Enterprise Questions

Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

AspenN — Thu, 11 Jan 2024 19:56:21 GMT

We are joining non-spatial tables in a PostgreSQL database to GIS feature classes (based on a common id). When the join occurs, a percentage sign (%) appears in the joined table name and field names. This causes weird behaviors in Pro (sometimes not allowing symbolizing based on joined fields) and doesn't work well when referencing the fields using the JS API. Is there a work-around for this issue? It seems like a pretty common workflow (joining a database table to a feature class) to be having such a serious issue, especially when the bug was first reported in 2014 (10 years ago).

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

TanuHoque — Fri, 12 Jan 2024 02:02:14 GMT

@AspenN

here are two that I can think of. I'm afraid you might not like either of them :(.

register your table to your geodatabase, or
create a query layer where you join your fc to the table using SQL statement.
- pls note, this requires both the feature class and table inside the same database. Cross workspace won't work

Here is the bug# BUG-000156380 that we have on our side to resolve this issue.

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

berniejconnors — Thu, 11 Jan 2024 20:16:46 GMT

It is bad practice to use the SQL wildcard character ("%") in database table names or column names. Changing your table names and column names would be a good idea but it may not be practical if you have many systems that already reference your tables and columns.

Bernie.

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

MarceloMarques — Thu, 11 Jan 2024 21:21:18 GMT

another option is to create a database view.
|
Views in an enterprise geodatabase—ArcGIS Pro | Documentation

Example: Create database views in PostgreSQL using SQL—ArcMap | Documentation (arcgis.com)

Create Database View (Data Management)—ArcGIS Pro | Documentation

SQL and enterprise geodatabases—ArcMap | Documentation (arcgis.com)

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

AspenN — Thu, 11 Jan 2024 21:29:34 GMT

For the query layer, do you have an example of an SQL statement to create the join?

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

AspenN — Thu, 11 Jan 2024 21:30:41 GMT

The % is added automatically during the join process by the software. We are hoping to find a solution to remove the %

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

MarceloMarques — Thu, 11 Jan 2024 23:09:11 GMT

Example: Create database views in PostgreSQL using SQL—ArcMap | Documentation (arcgis.com)

Create a view to join two tables

In this example, the view—emp_regions_view—joins a spatial table (feature class) with a nonspatial table based on the emp_id column. The view includes the ObjectID, spatial column (region), and region name (rname) from the regions feature class along with the employee name and ID from the employees table.

CREATE VIEW emp_regions_view 
 AS SELECT (e.emp_name,e.emp_id,r.objectid,r.rname,r.region)
 FROM employees e, regions r
 WHERE e.emp_id = r.emp_id;

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

TanuHoque — Thu, 11 Jan 2024 23:03:00 GMT

@AspenN

here is an example:

SELECT fc.*, tbl.*
FROM myFC fc
INNER JOIN my_non_reg_table tbl
ON fc.joinFld = tbl.joinFld

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

AspenN — Thu, 11 Jan 2024 23:10:42 GMT

Thank you for the examples @TanuHoque and @MarceloMarques . However, I am getting the error "Underlying DBMS error[ERROR: cross-database references are not implemented" when i try to use the code examples.

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

MarceloMarques — Thu, 11 Jan 2024 23:20:53 GMT

@AspenN
cross-database references are not implemented
read more in the link below on how to resolve this problem.
https://stackoverflow.com/questions/51784903/cross-database-references-are-not-implemented

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

TanuHoque — Fri, 12 Jan 2024 02:04:06 GMT

urgh.... I wanted to mention that in my first comment and then I dropped (I just updated that comment) -- that the query layer approach requires both side presented in the same database.

thanks @MarceloMarques for providing the link to the stackoverflow discussion

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

HeathClark — Tue, 16 Jan 2024 16:09:11 GMT

@TanuHoque - As this issue has been outstanding for over 10 years... is there a definitive schedule to get this bug fixed?

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

MarceloMarques — Tue, 16 Jan 2024 19:25:04 GMT

@HeathClark - Esri Technical Support status on the BUG.

------------------------------------------------------------------------------------------------------------------------------------------------------
BUG-000149858 - The Join name and Destination table name contain a percentage symbol (%) after creating a join between a feature class and a stand-alone table in ArcGIS Pro.

https://support.esri.com/en-us/bug/the-join-name-and-destination-table-name-contain-a-perc-bug-000149858

Duplicate of BUG-000156380: This bug does not state a problem. The query layer naming of joined tables using the % is general to all enterprise databases. The associated issue, BUG-000156380, indicates a publishing problem that some may be experiencing.

Status: Duplicated.

------------------------------------------------------------------------------------------------------------------------------------------------------

BUG-000156380 - Unable to query the map service from the REST endpoint using the fully qualified name when applying a join with a feature class and a non-registered table.

https://support.esri.com/en-us/bug/unable-to-query-the-map-service-from-the-rest-endpoint-bug-000156380

Status: In Product Plan

Workaround: Instead of a fully qualified name, use only the field name.

------------------------------------------------------------------------------------------------------------------------------------------------------

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

TanuHoque — Tue, 16 Jan 2024 19:58:46 GMT

@HeathClark -

This defect has presented many challenges. The resolution we are working toward is for map services. Based on current progress, we are working to provide a resolution in one of the upcoming releases in 2024.

Re: Is there a work-around for BUG-000149858 (join name and destination table name contain a percentage symbol after creating a join)?

berniejconnors — Wed, 17 Jan 2024 15:34:04 GMT

xkcd for the win!

https://xkcd.com/2881