cancel
Showing results for 
Search instead for 
Did you mean: 

EXCEPT Join in PaPM

Former Member
0 Kudos
1,116

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.

Accepted Solutions (0)

Answers (3)

Answers (3)

Angelou
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

Former Member
0 Kudos

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.

ZYT
Product and Topic Expert
Product and Topic Expert
0 Kudos

I reckon your approach is already optimal.

Angelou
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

ZYT
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Angelou,

you'd need to use an auto filling option to achieve it. And your selection (complex selection unnecessary) should be read as EXCLUDE_FLAG !=''.