CDS View - Left Excluding Join

[celo_berger]

Hello CDS experts,

I have the following requirement:

1. I have the sales table VBAK with sales docs
2. I have the billing table VBRP with billing docs and the link to sales docs (AUBEL/AUPOS);
3. I need to select Sales Docs from VBAK that do NOT exist in VBRP using CDS views:

In the example above, I only need to select Sales Docs 4 and 5 from VBAK, since 1-3 exist in VBRP.

This would be a LEFT EXCLUDING, (or a LEFT OUTER - INNER join) but I'm not sure this syntax is available:

Any ideas on how to do this through CDS Views?

Much appreciated!

Marcelo

[cmotasan]

Hi,

To perform an exclusion join in a CDS view, join the base table and the exclusion table with LEFT OUTER JOIN. Then, use WHERE IS NULL in the exclusion table to select those values in the base table that couldn't join with the exclusion table.

In your case, you'll need to perform a VBAK LEFT OUTER JOIN VBRP, then use WHERE VBRP.mandt IS NULL (you can use the MANDT field or any other field of your choice) to show those values in VBAK that couldn't join with VBRP.

Best regards,

Carlos