Skip to Content
Jul 04, 2018 at 07:47 PM

Need help with CDS view creation with multiple LEFT JOIN's & WHERE condition



I am trying create a CDS view to pull data from 4 tables (ACDOCA, KNA1, MAKT & ADRC). My requirement is to show all records from ACDOCA even if there are no corresponding referenced records in KNA1 or MAKT. Below is how I have written it:

@AbapCatalog.sqlViewName: 'ZV_CDS_TEST'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS Test View'
define view ZVA_CDS_TEST as select from acdoca 
left outer join kna1
    on kna1.kunnr = acdoca.kunnr
left outer join makt
    on makt.matnr = acdoca.matnr
left outer join adrc
    on adrc.addrnumber = kna1.adrnr
  acdoca.poper as poper,
  acdoca.budat as budat,
  acdoca.gjahr as gjahr,
  acdoca.rbukrs as rbukrs,
  acdoca.re_account as re_account,
  kna1.erdat as erdat,
  acdoca.matnr as matnr,
  makt.maktx as maktx,
  acdoca.kunnr as kunnr,
  kna1.name1 as name1,
  kna1.brsch as brsch,
  kna1.adrnr as adrnr, as country,
  acdoca.rtcur as rtcur,
  acdoca.tsl as tsl,
  adrc.date_to as date_to
} where 
    makt.spras = 'E' AND
    adrc.date_to = '99991231'

Now, with above I don't see all records from table ACDOCA because of the WHERE clause but at the same time, I would like to restrict the MAKT & ADRC records based on these conditions.

Can this be done via CDS or must I split this into multiple CDS views or am I better off creating a report with multi SELECT queries and consolidation logic?