idea User-defined SQL join clause in ArcGIS Pro Ideas

User-defined SQL join clause

Bud — Thu, 15 Dec 2022 04:19:49 GMT

When creating a dynamic/in-map join, it would be helpful if we could specify the join clause using SQL.

For example, join on multiple fields:
Instead of joining only on EVENT_ID=EVENT_ID, join on multiple fields: EVENT_ID=EVENT_ID AND YEAR=YEAR.

Re: Manually specify join clause using SQL

KoryKramer — Thu, 25 Aug 2022 13:16:41 GMT

Thanks for submitting the idea @Bud Note that there is an existing idea to Join based on multiple fields

If that is the need, I'd say we can merge your idea with that one. Note in the comments, though, that you could try using the Make Query Layer tool.

Re: Manually specify join clause using SQL

Bud — Thu, 25 Aug 2022 13:22:00 GMT

@KoryKramer

Thanks. I think it's worth keeping this idea separate. There are other cases where specifying the SQL would be useful. For example, using an SQL expression or function in the join clause.

Re: Manually specify join clause using SQL

Bud — Wed, 07 Dec 2022 12:55:28 GMT

This is how it’s done in other enterprise platforms.

You define an SQL WHERE clause for the join. You can join on multiple fields if you need to. And include complex expressions, case statements, subqueries, etc.

If you want to pare down a 1:M join to a 1:1 join, you can do that in the SQL clause too, using ORDER BY, FETCH FIRST ROW ONLY, etc. or just use a subquery.

Source: Field based on relationship: What if relationship is 1:M? and https://www.bpdzenith.com/the-bpd-blog/maximo-relationships

It’s similar to the idea of having a user-defined ORDER BY clause in a join: One-to-first joins: Control what related record is used

Add join/join field with expression

wayfaringrob — Wed, 14 Dec 2022 21:39:44 GMT

Allow a table to be joined to another table/layer using an expression as the input join field. This would allow users to more easily join data together when a key field is flawed or nonexistent but could be calculated with an expression. Ex. I have a TAXDIST field whose first two digits tell me the municipality, and I have a table with these two digits and their names that I'd like to join, but I can't do this without first adding adding and calculating a separate field to the input table, which I then wouldn't need after joining.

Field from the input table:

Cannot join this table without creating a new field in the input table and calculating with a Left() expression.

Idea is to allow the user to just enter the expression here:

with one of these bad boys:

Re: Add join/join field with expression

Bud — Thu, 15 Dec 2022 04:22:26 GMT

Related: User-defined SQL join clause