cancel
Showing results for 
Search instead for 
Did you mean: 

How to do reverse of a left outer join in a calculation view

0 Kudos

Hi,

I am trying to do what is essentially a reverse of a left outer join in a graphical calculation view. but I am not able to figure out how.

my scenario - I have a table say VBAP. Through some inner joins with other tables , I end with a subset of VBAP Lets call it VBAP_SUBSET.

Now I need to display results which were originally in VBAP but not in VBAP_SUBSET.

or in other words, my result set should be all records that did not satisfy the join condition that I used to define the subset.

primary key of VBAP and VBAP_SUBSET is the same.

So in the picture below, my result should be the blue area.

looking for advice/guidance.

Accepted Solutions (1)

Accepted Solutions (1)

SergioG_TX
Active Contributor
0 Kudos

in sql words, wouldnt this be a not in clause?

so use the main view/table.. .then not in (select from subset )  ??

or attempt a right join.. and select the conditions which are null... ?? maybe another approach

0 Kudos

yeah, you are right. thats how I would do it in sql too.

but I am not able to do in a graphical calculation view. so looking for ideas. I am multiple instances of this scenario so would do like to stick to a graphical Calc View rather than Script based view.

SQL for what i am trying to do would be something like this

SELECT * FROM TABLE VBAK A LEFT JOIN VBAK_SUBSET B ON A.KEY = B.KEY  WHERE B.KEY IS NULL

Given my primary key can be multiple fields. I just need to figure out how to implement this WHERE clause in a Calc view - I think I will be set.

Astrid_Gambill
Contributor
0 Kudos

In the join node with the left outer join type, add the primary key(s) from the right hand table to the output.  Then add a projection node above the join, and add filters to the columns from the right hand table, with ISNULL.

0 Kudos

yes. this worked like a charm. once you figure out which side to use filter ISNULL and which side to display in the output (which get little tricky since the primary keys on both sides is the same ) - it worked fine.

Thanks for the quick tip.

Answers (0)