ABAP on HANA之CDS Association和Path Expression

本文闡述了ABAP CDS association的概念，并且展示了在CDS視圖中和SQL語句中寫路徑表達式（Path Expression）代碼的方法。我也會解釋如何在CDS asociation中指定inner join——預設情況下是left outer join，以及如何為association添加過濾。

對于CDS的相關開發，SAP希望我們使用association而不是join，因為association更加接近“概念思維”。基本上，association本身不是join，它隻是有關join連接配接可能性的中繼資料，它會按需成為join。真實的join會在路徑表達式使用association的時候被建立。

 一個簡單的CDS association例子，它看起來和left outer join沒差別：

@AbapCatalog.sqlViewName: 'ZCDS_ASSOC11'
define view zcds_assoc1 as select from scarr as sca
   association [0..1] to spfli as _spfli 
   on sca.carrid = _spfli.carrid   
   { * }      

暴露CDS association的例子：

@AbapCatalog.sqlViewName: 'ZCDS_ASSOC41'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view zcds_assoc4 as select from sairport as sair
    association [1..*] to spfli as _spfli on
        $projection.airportfrom = _spfli.airpfrom
    {     
       sair.id as airportfrom,
       sair.name,
       sair.time_zone,
      -- exposing association
      _spfli
    }      

在下面的例子裡，你可以看到SPFLI表對SFLIGHT表和SAIRPORT表的association。通過别名alias _sfli和SFLIGHT和_sair，SAIRPORT的全部字段暴露在projection清單中。當路徑表達式用于調用association時，會根據選擇字段建立join條件：spfli.carrid = sflight.carrid and spfli.connid = sflight.connid and on spfli.airport = sairport.id。

@AbapCatalog.sqlViewName: 'ZCDS_ASSOC21'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view zcds_assoc2 as select from spfli 
   association to sflight as _sfli on 
        spfli.carrid = _sfli.carrid and
        spfli.connid = _sfli.connid
    association [1..1] to sairport as _sair on
        $projection.airportfrom = _sair.id
    {
      spfli.carrid,
      spfli.connid,
      spfli.airpfrom as airportfrom,
      -- exposing association
      _sfli,
      _sair    
    }      

在下面的例子裡，定義association時使用了上面的CDS entity，ZCDS_ASSOC2 ：

@AbapCatalog.sqlViewName: 'ZCDS_ASSOC31'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view zcds_assoc3 as select from scarr
        association [1..1] to zcds_assoc2  as _cdsassoc on
         $projection.carrierid = _cdsassoc.carrid
      {
         scarr.carrid as carrierid,
     -- Exposing Association    
         _cdsassoc
      }       

Open SQL語句中的路徑表達式：在SQL語句中調用CDS association，需要使用如下的路徑表達式。在上面的CDS association中，通過_cdsassoc暴露了完整的projection清單。

DATA(w_dbfeature) = cl_abap_dbfeatures=>use_features( 
　requested_features = VALUE #( ( cl_abap_dbfeatures=>views_with_parameters ) ) ).
IF w_dbfeature IS NOT INITIAL.
  SELECT  carrierid ,
          \_cdsassoc\_sfli-fldate AS flightdate,
          \_cdsassoc\_sair-name AS flightname
        FROM zcds_assoc3
        WHERE carrierid = @carrid
        INTO TABLE @DATA(t_data1).
ENDIF.      

CDS視圖中的路徑表達式：

@AbapCatalog.sqlViewName: 'ZCDS_ON_ASSOC1'
define view zcds_on_assoc with parameters airport: S_FROMAIRP  as select from zcds_assoc2 as cds2
    {
      cds2.carrid,
      cds2.connid,
      cds2.airportfrom,    
      cds2._sair.name, -- use inner join...by default association uses left outer join
      cds2._sfli.planetype
    }
 where cds2.airportfrom = :airport       

如我所提到的那樣，在被路徑表達式調用時，CDS association會預設建立left outer join。

在SPFLI表資料中，沒有RTM機場的航班。

當我們輸入參數airport = RTM的時候 ，下面的CDS視圖的查詢結果會是一條RTM機場的資料，但是這條記錄裡沒有carrid。

@AbapCatalog.sqlViewName: 'ZCDS_ON_ASSOC41'
define view zcds_on_assoc4 with parameters airport: S_FROMAIRP as select from zcds_assoc4 as cds_assoc
    {
      cds_assoc.airportfrom,
      cds_assoc.name,
      cds_assoc.time_zone,    
      cds_assoc._spfli.carrid -- use inner join...by default association uses left outer join
    }
 where cds_assoc.airportfrom = :airport       

運作上面的CDS視圖：

data preview中的結果：

為了把預設的left outer join變成inner join，我們需要使用[inner]，如下：

@AbapCatalog.sqlViewName: 'ZCDS_ON_ASSOC41'
define view zcds_on_assoc4 with parameters airport: S_FROMAIRP as select from zcds_assoc4 as cds_assoc
    {
      cds_assoc.airportfrom,
      cds_assoc.name,
      cds_assoc.time_zone,    
      cds_assoc._spfli[inner].carrid -- use inner join...by default association uses left outer join
    }
 where cds_assoc.airportfrom = :airport       

如果運作它，輸入參數RTM，得到的結果為空：

目前，還不可以在CDS association中使用right outer join。

CDS association中的過濾例子如下：

@AbapCatalog.sqlViewName: 'ZCDS_ASSOC_FILT'
define view ZCDS_ASSOC_FILTER  as select from zcds_assoc2 as cds2
    {
      cds2._sair[ id = 'TYO' ].name,
      cds2._sfli.planetype
    }      

CDS視圖的輸出結果：

希望本文對你有幫助！

本文連結：https://www.cnblogs.com/hhelibeb/p/9202781.html

英文原文：CDS Associations and 路徑表達式s – ABAP on HANA

參考閱讀：ABAP 7.52 中的Open SQL新特性

　　　　   Material Quantity Unit Conversion using CDS Views

　　　　   HANA CDS與ABAP CDS