MCHB & MCHBH CDS View

CDS View를 재활용하여 CDS view를 만들때에는 association을 이용하여 만들어주어야 한다.

@AbapCatalog.sqlViewName: 'ZQMCV_SQLV_0001'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@ClientDependent: true

@EndUserText.label: 'MCHBH cds view test'

define view ZQMCV_0001Z as 

select from nsdm_e_mchbh as hbh

association [1] to nsdm_e_mchb as hb

    on hb.matnr = hbh.matnr

   and hb.werks = hbh.werks

   and hb.lgort = hbh.lgort

   and hb.charg = hbh.charg

{    

    key hbh.matnr,

    key hbh.werks,

    key hbh.lgort,

    key hbh.charg,

    

    key concat(hbh.lfgja, hbh.lfmon) as h_yearmon,

    concat(hb.lfgja,  hb.lfmon)  as yearmon,

    

    hbh.clabs as h_CLABS,

    hbh.cumlm as h_CUMLM,

    hbh.cinsm as h_CINSM,

    hbh.ceinm as h_CEINM,

    hbh.cspem as h_CSPEM,

    hbh.cretm as h_CRETM,

        

    hb.clabs,

    hb.cumlm,

    hb.cinsm,

    hb.ceinm,

    hb.cspem,

    hb.cretm

    

} 

4개의 cds view를 이용하여 max h_yearmon의 값을 한꺼번에 가져오는 CDS view 생성

=======================================================================

** MCHB와 MCHBH의 data join

@AbapCatalog.sqlViewName: 'ycds_ass_trest01'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'cds association test'

define view yass_cds_test01_01 as 

select from nsdm_e_mchbh as hbh

association [1] to nsdm_e_mchb as hb

    on hb.matnr = hbh.matnr

   and hb.werks = hbh.werks

   and hb.lgort = hbh.lgort

   and hb.charg = hbh.charg 

   {

    

    key hbh.matnr,

    key hbh.werks,

    key hbh.lgort,

    key hbh.charg,

    

    key concat(hbh.lfgja, hbh.lfmon) as h_yearmon,

    concat(hb.lfgja,  hb.lfmon)  as yearmon,

    

    hbh.clabs as h_CLABS,

    hbh.cumlm as h_CUMLM,

    hbh.cinsm as h_CINSM,

    hbh.ceinm as h_CEINM,

    hbh.cspem as h_CSPEM,

    hbh.cretm as h_CRETM,

        

    hb.clabs,

    hb.cumlm,

    hb.cinsm,

    hb.ceinm,

    hb.cspem,

    hb.cretm

}  

 

=======================================================================

** MCHBH의 hbh.lfgja, hbh.lfmon concat

@AbapCatalog.sqlViewName: 'ycds_ass_trest02'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'cds association test 2'

define view yass_cds_test01_02 as 

select  

    key hbh.matnr,

    key hbh.werks,

    key hbh.lgort,

    key hbh.charg,    

    concat(hbh.lfgja, hbh.lfmon) as h_yearmon

from nsdm_e_mchbh as hbh

group by hbh.matnr,

    hbh.werks,

    hbh.lgort,

    hbh.charg,

    hbh.lfgja, hbh.lfmon

   

========================================================================

** MCHBH의 max h_yearmon data table

@AbapCatalog.sqlViewName: 'ycds_ass_trest04'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'cds association test 4'

define view yass_cds_test01_04 as

select   

    key hbh.matnr,

    key hbh.werks,

    key hbh.lgort,

    key hbh.charg,    

    max(h_yearmon) as h_yearmon

from yass_cds_test01_02 as hbh

group by hbh.matnr,

    hbh.werks,

    hbh.lgort,

    hbh.charg 

 

=======================================================================

** MCHB와 MCHBH 중 h_yearmon 값이 max 인 데이타 select

 

@AbapCatalog.sqlViewName: 'ycds_ass_trest03'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'cds association test 3'

define view yass_cds_test01_03 as 

select from yass_cds_test01_01 as hbh

   inner join yass_cds_test01_04 as hbm

    on hbh.matnr = hbm.matnr

   and hbh.werks = hbm.werks

   and hbh.lgort = hbm.lgort

   and hbh.charg = hbm.charg 

   and hbh.h_yearmon = hbm.h_yearmon {

    

    key hbh.matnr,

    key hbh.werks,

    key hbh.lgort,

    key hbh.charg,

    

    key hbm.h_yearmon,

    hbh.yearmon,

    

    hbh.h_CLABS,

    hbh.h_CUMLM,

    hbh.h_CINSM,

    hbh.h_CEINM,

    hbh.h_CSPEM,

    hbh.h_CRETM,

        

    hbh.clabs,

    hbh.cumlm,

    hbh.cinsm,

    hbh.ceinm,

    hbh.cspem,

    hbh.cretm

}