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

[AspenN]

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). 

[TanuHoque]

@AspenN 

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

1. register your table to your geodatabase, or
2. 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.

[MarceloMarques]

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)

[AspenN]

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

[MarceloMarques]

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;

[TanuHoque]

@AspenN 

here is an example:

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

[AspenN]

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.  

[MarceloMarques]

@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

[TanuHoque]

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

[HeathClark]

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