Search code examples
abapcds

How to achieve NOT EXISTS with the ABAP CDS?

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.

Solution

  • 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.