“NOT EXISTS” Subqueries in ABAP CDS Views

ABAP CDS Views are great and all; however, they don’t support subqueries at this time. In this post, I would like to share a workaround which functions similar to “NOT EXISTS”.

The requirement was to exclude material document items (MSEG) which were reverse posted; and the reversal documents themselves.

If I was writing a regular ABAP SQL statement, I could easily achieve this with the help of a NOT EXISTS subquery. Check the code snippet below – I have excluded unrelated WHERE conditions for simplicity.

SELECT mblnr, mjahr, zeile 
  FROM mseg AS mseg1 
  WHERE 
    ( sjahr EQ '0000' OR sjahr IS NULL )
    AND
    ( NOT EXISTS (
      SELECT mandt FROM mseg AS mseg2 WHERE
        sjahr EQ mseg1.mjahr AND
        smbln EQ mseg1.mblnr AND
        smblp EQ mseg1.zeile
    ) )
  INTO TABLE @DATA(lt_mseg).

This logic doesn’t work in CDS views directly due to lack of subquery support. To get the same result using CDS views, I have split the query into two views. The first one returns a wide list where original and reversal MSEG records are listed side by side.

@AbapCatalog.sqlViewName: 'ZMMV_001'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Original and reversal docs side by side'
define view ZMMV_MAT_DOC_ITEM_REV_STATUS as 
  select from 
    mseg as mseg1
    left outer join mseg as mseg2 on
      mseg2.sjahr = mseg1.mjahr and
      mseg2.smbln = mseg1.mblnr and
      mseg2.smblp = mseg1.zeile
  {
    mseg1.mblnr as mblnr1,
    mseg1.mjahr as mjahr1,
    mseg1.zeile as zeile1,
    mseg1.sjahr as sjahr1,
    mseg1.smbln as smbln1,
    mseg1.smblp as smblp1,
    mseg2.mblnr as mblnr2,
    mseg2.mjahr as mjahr2,
    mseg2.zeile as zeile2,
    mseg2.sjahr as sjahr2,
    mseg2.smbln as smbln2,
    mseg2.smblp as smblp2
  }

The second view filters out the reversed & reversal documents from the first view, which leaves effective material documents only.

@AbapCatalog.sqlViewName: 'ZMMV_002'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Effective material document items'
define view ZMMV_EFFECTIVE_MAT_DOC_ITEM as 
  select distinct from ZMMV_MAT_DOC_ITEM_REV_STATUS
  {
    key mblnr1 as mblnr,
    key mjahr1 as mjahr,
    key zeile1 as zeile
  }
  where
    sjahr1 = '0000' and
    (
      mjahr2 = '0000' or
      mjahr2 is null
    ) and
    (
      sjahr2 = '0000' or
      sjahr2 is null
    )

This approach can be implemented to replace NOT EXISTS in many other cases as well.