CDS视图的基本语法，函数以及数量，货币类型转换

最新推荐文章于 2024-04-10 21:55:42 发布

Gong JX

最新推荐文章于 2024-04-10 21:55:42 发布

阅读量3.2k

点赞数

分类专栏： CDS 文章标签： CDS 数据库架构 sql

本文链接：https://blog.csdn.net/qq_40977705/article/details/116190105

版权

CDS 专栏收录该内容

9 篇文章 11 订阅

订阅专栏

CDS TABLE FUNCTION，AMDP

测试参考代码

CDS视图基本语法介绍

数据强制类型转换

强制类型转换使用cast函数，支持ABAP的所有类型之间转换

cast( '20210901' as abap.int4 ) as col_int4,
cast( '20210901' as abap.dec( 10, 2 )) as col_des,
cast( '20210901' as abap.dats ) as col_date ,
cast( '20210901' as abap.char( 8 )) as col_char,
...

字符串操作

concat(carrid , connid) as carrid_and_connid ,
concat_with_space('12345', '789', 1) as col_concatwithspace, // 第三个参数是空格数
substring('123' , 1, 2 ) as col_sub , // 截取1~2位
left('12345' ,3) as col_left, // 向左移至第三位
right('12345' , 3) as col_right,
replace('123456789', '1234', '4321') as col_replace ,

结果参考

条件语句

case fltype
  when ' ' then 3
  when 'X' then 1
  else 0
  end as Criticality,

case 
 when _scarr.carrid = 'AA' then 'THIS IS AA'
 when _scarr.carrid = 'AB' or _scarr.carrid = 'AC' then 'NEXT'
 else 'NULL'
 end as TEST_CASE

货币类型转换

不同数据的货币价格对应不同的价格单位，有USD，EUR，JPY，把它们统一以美元的形式展示出来，不同的货币类型之间要做根据汇率去换算，下面这个函数可以一步到位

@Semantics.amount.currencyCode: '_sflight.currency,'
  currency_conversion( amount => _sflight.price ,
                       source_currency => _sflight.currency,
                       target_currency => cast('USD' as abap.cuky),
                       exchange_rate_date => _sflight.fldate
                       ) as price ,
  cast( 'USD' as abap.cuky ) as currency ,

数量转换

和货币一样，统一数量单位

@Semantics.quantity.unitOfMeasure: 'spfli.distance'
 Unit_Conversion (
    quantity => spfli.distance,
    source_unit => spfli.distid,
    target_unit => cast( 'MI' as abap.unit( 3 ) ) ) as distance ,
  cast( 'MI' as abap.unit ) as distid ,

日期函数

参数传递

CDS VIEW使用parameters传递参数，带参数的CDS VIEW，不能直接发布成一个Odata服务，只能被消费

当cds在被消费的时候，参考以下用法

    select * from ZZ_CSDN_DEMO( P_CARRID = @Lv_CARRID  )
    into TABLE @data(lt_ZZZ_CSDN_DEMO).

CDS TABLE FUNCTION，AMDP

amdp涉及到很多新的概念，以下链接详细介绍

https://blog.csdn.net/qq_40977705/article/details/115932873

测试参考代码

@AbapCatalog.sqlViewName: 'ZV_CSDN_DEMO'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Flight Information'
@UI.headerInfo: {typeName: 'Flight', typeNamePlural: 'Flights',
title: { value: 'carrname'}, description: { value: 'connid' }}
//@OData.publish: true
define view ZZ_CSDN_DEMO
with parameters P_CARRID: abap.char( 3 )
as select from spfli
association [1..1] to scarr as _scarr     on $projection.carrid = _scarr.carrid
association [1..1] to sflight as _sflight on $projection.carrid = _sflight.carrid
{

//spfli
@UI: {
lineItem: [{ position: 10, label: 'Carrier Name'}],
identification: [{position: 10, label: 'Carrier Name'}]
}
key carrid,

@UI: {
lineItem: [{ position: 20, label: 'Connection'}],
identification: [{position: 20, label: 'Connection'}]
}
key connid,

cast( '20210901' as abap.int4 ) as col_int4,
cast( '20210901' as abap.dec( 10, 2 )) as col_des,
cast( '20210901' as abap.dats ) as col_date ,
cast( '20210901' as abap.char( 8 )) as col_char,

concat(carrid , connid) as carrid_and_connid ,
concat_with_space('12345', '789', 1) as col_concatwithspace, // 第三个参数是空格数
substring('123' , 1, 2 ) as col_sub , // 截取1~2位
left('12345' ,3) as col_left, // 向左移至第三位
right('12345' , 3) as col_right,
replace('123456789', '1234', '4321') as col_replace ,

_scarr.carrname as carrname,
@Semantics.quantity.unitOfMeasure: 'spfli.distance'
 Unit_Conversion (
    quantity => spfli.distance,
    source_unit => spfli.distid,
    target_unit => cast( 'MI' as abap.unit( 3 ) ) ) as distance ,
  cast( 'MI' as abap.unit ) as distid ,
  
@Semantics.amount.currencyCode: '_sflight.currency'
  currency_conversion( amount => _sflight.price ,
                       source_currency => _sflight.currency,
                       target_currency => cast('USD' as abap.cuky),
                       exchange_rate_date => _sflight.fldate
                       ) as price ,
  cast( 'USD' as abap.cuky ) as currency ,
  dats_add_days(_sflight.fldate, 1, 'FAIL') as fldates ,
@UI: {
lineItem: [{ position: 30, label: 'Strat Country'}],
fieldGroup: [{ qualifier: 'Coun', position: 10, label: 'Strat Country' }]
}
countryfr,

@UI: {
lineItem: [{ position: 40, label: 'Arriving Country'}],
fieldGroup: [{ qualifier: 'Coun', position: 20, label: 'Arriving Country' }]
}
countryto,

@UI: {
lineItem: [{ position: 50, label: 'Start City'}],
fieldGroup: [{ qualifier: 'City', position: 10 }]
}
cityfrom,

@UI: {
lineItem: [{ position: 60, label: 'Arriving City'}],
fieldGroup: [{ qualifier: 'City', position: 20 }]
}
cityto,

@UI: {
lineItem: [{ position: 70, label: 'Start Airport'}],
fieldGroup: [{ qualifier: 'Airp', position: 10 }]
}
airpfrom,

@UI: {
lineItem: [{ position: 80, label: 'Arriving Airport'}],
fieldGroup: [{ qualifier: 'Airp', position: 20 }]
}
airpto,

@UI: {
fieldGroup: [{ qualifier: 'Time', position: 10 }]
}
deptime,

@UI: {
fieldGroup: [{ qualifier: 'Time', position: 20 }]
}
arrtime,
//position: 90,,
//fieldGroup: [{ qualifier: 'Xinfo', position: 10 }]
@UI: {
lineItem: [{  label: 'Flight Type', criticality: 'Criticality' }]
}
fltype,

case fltype
  when ' ' then 3
  when 'X' then 1
  else 0
  end as Criticality,

case 
 when _scarr.carrid = 'AA' then 'THIS IS AA'
 when _scarr.carrid = 'AB' or _scarr.carrid = 'AC' then 'NEXT'
 else 'NULL'
 end as TEST_CASE
 
}where spfli.carrid = $parameters.P_CARRID