CDS 测试 汇集

Association  和 Inner Join， Left Outer Join， Right Outer Join 关系。

association 是一种lazy join的behavior，只有当field真正被另一个view用到的时候才会去做join

View A   Association to  View B, 若用不到VIEW B的字段时，不会取B的数据

Inner Join， Left Outer Join， Right Outer Join在runtime 时会取全有字段，不管用不用

 取得当前系统日期 $session.system_date as systemdate

取得当前系统时间戳   tstmp_current_utctimestamp()

Inner Join， Left Outer Join， Right Outer Join的用法同ABAP OPEN SQL的用法一致

• UNION可以合并两个SELECT的结果集，并自定去除重复的条目
• UNION ALL 合并结果集，保留重复的条目
• 合并的两个结果集要有相同字段数
• 合并结果集的列类型要兼容
• 字段名称要相同

SAP 查找标准CDS视图的方式 - 宁海峰 - 博客园

1. 打开地址：https://fioriappslibrary.hana.ondemand.com/sap/fix/externalViewer/
2. 选择All apps for SAP S/4HANA->All apps
3. 在清单中选择相应的应用程序，此处Demo选择：Change Process Analysis for Business Partner
4. 在Detail View中选择IMPLEMENTATION INFORMATION
5. 点开：Configuration
6.  在OData Services(s)中得到OData Service的名字：MDC_PROC_BP_ALP_SRV
7. 在S/4HANA系统中执行T-Code：SEGW，打开项目：MDC_PROC_BP_ALP（将6中的服务名称去掉后缀_SRV

 CL_SALV_GUI_TABLE_IDA=>CREATE_FOR_CDS_VIEW( iv_cds_view_name = 'ydemo_c_rstatus' )->FULLSCREEN()->DISPLAY().

CASE1： CASE ,CONCAT substring cast

coalesce（arg1, arg2）: 如果arg1不为NULL则返回arg1的值，否则返回arg2的值

 
define view ZDEMO_CDS_D002 as select from yekko as a
inner join yekpo as b on a.ebeln = b.ebeln
 {
key  a.ebeln,

coalesce( smoker, 'unassigned') as somker_status,

 concat( concat (substring(order_date,5,2),'-'),

  substring(order_date,1,4) ) as ordermonth，

  forcuram,
   case smoker
            when 'X' then cast ( forcuram as abap.fltp ) * 1.3
            else          cast ( forcuram as abap.fltp ) * 0.9
       end as final_amount

} 

CASE2： distinct

define view ZDEMO_CDS_DDL

as select distinct from sbook

{ carrid }

case3 聚集
define view ZDEMO_CDS_DDL
  as select from sflight
{
  planetype,
  min(price) as min_price,
  max(price) as max_price,
  sum(price) as sum_price,
  count(*)   as count_planes

}
group by
  planetype
 having planetype = '747-400' or count(*) > 60

CASE 5 

测试 先定义一个Header 的VIEW，包含ITEM，CUSTOMER,VENDOR,等信息，

然后以Header 这个视图，再定义ITEM 等视图，

Header 的VIEW  sales_order_invoice_header 

define view sales_order_invoice_header as

  select from snwd_so_inv_head

     inner join snwd_soon snwd_so_inv_head.so_guid = snwd_so.node_key

    association [1..1] to snwd_bpa as _buyer

       on $projection.buyer_guid = _buyer.node_key

    association [1..*] to snwd_so_inv_item as _invoice_items

       on $projection.node_key = _invoice_items.parent_key

{ key snwd_so_inv_head.node_key,      //used in assoc _invoice_items

    snwd_so_inv_head.buyer_guid,    //used in assoc _buyer

   snwd_so.so_id as sales_order_id,

   _buyer.bp_id as buyer_id,       //from assoc _buyer

   snwd_so_inv_head.payment_status,

   @Semantics.currencyCode

   snwd_so_inv_head.currency_code,

   @Semantics.amount.currencyCode: 'currency_code'

   snwd_so_inv_head.gross_amount,

   _invoice_items       //publish assoc _invoice_items

         }

where _buyer.bp_role = '001';       

 

The CDS view can be accessed in an ABAP program with a simple SELECT statement (Open SQL).

SELECT sales_order_id, buyer_id, payment_status 
       FROM sales_order_invoice_header 
       INTO CORRESPONDING FIELDS OF TABLE @itab.

定义一个ITEM VEIW, select  sales_order_invoice_header

@AbapCatalog.sqlViewName: 'SALESO_INVITM_VW'

define view sales_order_invoice_items as

  select from sales_order_invoice_header as header

  { header.sales_order_id,

    header._invoice_items.inv_item_pos as item_position,

   @Semantics.currencyCode

    header._invoice_items.currency_code,

   @Semantics.amount.currencyCode: 'currency_code'

    header._invoice_items.gross_amount }

CASE 6

@AbapCatalog.sqlViewName: 'YDEMO_SPFLI_S01'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS VIEW SPFLI'
@ObjectModel: {
   createEnabled,
   deleteEnabled,
   updateEnabled
}
define view YDEMO_SPFLI_01 
as select from spfli
association [0..1] to scarr as B
on B.carrid = spfli.carrid {
       key spfli.carrid,
       key B.carrname,
       key spfli.connid,
       spfli.cityfrom,
       spfli.cityto
}

@AbapCatalog.sqlViewName: 'YDEMO_SPFLI_S02'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'odat   a publish'
@OData.publish: true
@ObjectModel: {
   type: #TO_COMPOSITION_ROOT,   
   compositionRoot,
   semanticKey: ['Actor'],
   createEnabled,
   deleteEnabled,
   updateEnabled
}
define view YDEMO_SPFLI_02 as select from YDEMO_SPFLI_01 {
     @UI.lineItem : [{position:10}]
    key YDEMO_SPFLI_01.carrid as Jerryid,
    @UI.lineItem : [{position:20}]
    key YDEMO_SPFLI_01.carrname as name,
    @UI.lineItem : [{position:30}]
    key YDEMO_SPFLI_01.cityfrom as startLocation,
    @UI.lineItem : [{position:40}]
    key YDEMO_SPFLI_01.cityto as target,
    @UI.lineItem : [{position:50}]
    key YDEMO_SPFLI_01.connid
}