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

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

5201 Views

Hi,

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,
  adrc.country 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?

Thanks,

Shrinivas