on 2022 Feb 03 7:42 PM
I need to create an Except or Minus Join function in PaPM that will exclude records from a data source (table1) if records match records in a second data source (table2). The fields need to match across multiple fields. A simplified SQL example of this type of join might look something like this:
SELECT field1, field2, Value1
FROM table1 A
LEFT JOIN table2 B
ON A.field1 = B.field1 AND A.field2 = B.field2
WHERE B.field1 IS NULL AND B.field2 IS NULL
To create something similar in PaPM I created a Join function where R1 is a FROM "table1" and R2 is a Left Outer Join on "table2". The Join Predicates are R2.field1 = R1.field1 and R2.field2 = R1.field2. In the Complex Predicates I added the following WHERE clause.
WHERE
(R2.field1 IS NULL)
AND
(R2.field2 IS NULL)
Is this the best why to do an EXCEPT/MINUS Join in PaPM or is there simpler/better way to do this type of Join? I did try setting the Join Predicates to "Unequal", but that does not give me the correct results.
Request clarification before answering.
Hi Andrew,
You are welcome. Of course, we understand if this is something you are more comfortable with. Thank you as well for trying out our suggestion which you may keep as a reference just in case you have future scenarios where you might find it helpful.
Thanks and Best Regards,
Angelou
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Angelou.
Thank you for taking the time to reply. Using the WHERE to test for NULL in the Complex Selection seems to work fine. I was just curious if there was another way of doing it. Your explaination showed that there is, but that multiple join rules are required to get to the same result. For now I will stick with using the WHERE clause in the Complex Selection as it reduces the number of rules.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Andrew,
The simplified SQL example below is the right way to do an EXCEPT/MINUS operator.
SELECT field1, field2, Value1
FROM table1 A
LEFT JOIN table2 B
ON A.field1 = B.field1 AND A.field2 = B.field2
WHERE B.field1 IS NULL AND B.field2 IS NULL
To achieve excluding records from a data source (table1) if
records match records in a second data source (table2) in PaPM system we create
a simple scenario that we can offer to you as a workaround since PaPM does not have the EXCEPT
operator as of now.
Input Table:
LEFT TABLE
RIGHT TABLE
Note: We added EXCLUDE_FLAG Field wherein we can use it in Complex Selection condition to exclude matching data records.
JOIN FUNCTION CONFIGURATION:
Rule 1
FROM RULE:
Complex Selection: WHERE CH_EXCLUDE != 'NO'
Subview: EXCLUDE_FLAG NOT USED.
FROM RULE 2:
LEFT OUTER JOIN RULE 3:
Expected Result:
Hope this will suggestion will work on your end. If you still have additional questions, please don't hesitate to let us know.
Thanks and Best Regards,
Angelou
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
10 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.