User-defined SQL join clause
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.
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
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:
