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

[gadde_shrinivas]

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

[uladzislau_pralat]

Hi Gadde,

try to move your MAKT and ADRC selection to corresponding LEFT OUTER JOIN clause ON condition.

Regards, Ulad

[DoanManhQuynh]

I afraid you cant use join as you want because: A WHERE condition for a SELECT statement with joins is applied to the results set created using the joins. You can read about it here:

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abapselect_join.htm

Did you consider to use Association instead? There is filter inside association maybe you can try. Read more here:

https://blogs.sap.com/2017/03/07/inner-join-with-cds-associations-abap-on-hana/

[shaikm6786]

@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 AND
       makt.spras = 'E'
left outer join adrc
    on adrc.addrnumber = kna1.adrnr AND
       adrc.date_to = '99991231'
{
  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
}