How to achieve NOT EXISTS with the ABAP CDS?

Question

I would like to select all EKPO records that don't have an existing posting in MSEG table.

In ABAP SQL this can be done like below:

SELECT ebeln, ebelp FROM ekpo INTO TABLE @DATA(orders)
  WHERE NOT EXISTS ( SELECT ebeln FROM mseg 
                                  WHERE ebeln = ekpo~ebeln
                                    AND ebelp = ekpo~ebelp ).

The only solution I found is to create 2 CDS views, the first one to select all the orders with a record in MSEG and the second one being the negation of the first. But I would expect a cleaner solution so I wanted to ask here.

Johann Goltz Johann Goltz · Accepted Answer · 2020-03-10T10:02:00

Here's how we do it:

define view my_view as 
select from ekpo
association[0..1] to mseg
  on mseg.ebeln = ekpo.ebeln
  and mseg.ebelp = ekpo.ebelp
{
  ebeln,
  ebelp
}
where mseg.mandt is null

If no entry matching the criteria exists in mseg, all fields of the association will be null. Otherwise, mseg.mandt will never be null.

How to achieve NOT EXISTS with the ABAP CDS?

1 Answers