CDS View-Part8&Part9&Part10&Part16 内置函数

原文：
https://sapyard.com/abap-for-sap-hana-part-xxv-usage-of-built-in-functions-in-cds-part-i/
https://sapyard.com/abap-for-sap-hana-part-xxvi-usage-of-built-in-functions-in-cds-part-ii/
https://sapyard.com/abap-for-sap-hana-part-xxvii-usage-of-built-in-functions-in-cds-part-iii/
https://sapyard.com/cds-part-16-usage-of-built-in-functions-in-cds-iv/
https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abensql_functions_string.htm
https://sapyard.com/cds-part-19-finding-week-of-the-year-in-cds-views/

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

尊重版权，仅供个人学习

CDS中的内置函数

有两类可用的Functions：

1. SQL Functions
2. Built In Functions

数学函数

FUNCTION	DEFINITION	OUTPUT
ABS(arg)	In mathematics, the absolute value or modulus	x
CEIL(arg)	Hitting the Ceiling of the Floating Number.	Smallest integer number not less than the value of arg
DIV(arg1, arg2)	Conventional Division	Quotient
DIVISION(arg1, arg2, dec)	Conventional Division but with an additional feature of specificing deicmal places	The result is rounded to dec decimal places.
MOD(arg1, arg2)	Conventional Modulo Operation	Remainder
FLOOR(arg)	Largest integer number not greater than the value of arg.	More like scientific numbers
ROUND(arg, pos)	Rounded value of arg.	Rounding the Designated decimal point value

实例例子更好说明用法：

@AbapCatalog.sqlViewName: 'ZFLIGHT_SCH_V'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Built In Functions'
define view ZFLG_FNC_DDL as select distinct from sflight as a
{
 abs(-2) as Abs_Op,
 
 /*Ceil and Floor*/
 ceil(25.3) as Ceil_Op,
 floor(25.3) as Floor_Op,
 
 /*Division*/
 div(5,3) as Div_Op,
 division(5,3,5) as Div_Op2,
 mod(5,3) as Mod_Op,
 
 a.price as Flg_Price, 
 round( a.price,1) as Round_Op

参考结果：

字符串函数

Function	Description
Length	gives Length of String
Instr	finds the position of respective string within corresponding the field of the View
Concatenate	joining two strings
Concatenate with Space	Third Parameter in this function represents the Number of space between two strings
Left	gives us left most characters equal to argument passed
Lower	converts all into lower case [ Rather Subscript ]
Lpad & Rpad	first parameter is field, second is the OUTPUT Length after padding, string that is to be padded
Ltrim & Rtrim	first parameter is field, second is string or character that is to be removed
Replace second parameter finds the string to be replaced by the third
Substring	finds the string that you want – second parameter is starting position and third is how many characters
Upper	converts all characters of string into Upper case

参考代码：

@AbapCatalog.sqlViewName: 'ZSTR_FN_V1'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Built In Functions'
define view ZFNC_DDL as select distinct from makt as a
{
key a.matnr as Mat_Num,
a.maktx as Mat_Desc,
length( a.maktx ) as Des_Len, 
instr( a.maktx, 'est' ) as Des_Find,
concat( a.maktx, a.spras ) as Des_Con,
concat_with_space( a.maktx, a.spras, 2 ) as Des_Con_space,
left( a.maktx, 3 ) as Des_left,
lower( a.maktx ) as Des_lower
}

参考结果：

参考代码：

@AbapCatalog.sqlViewName: 'ZSTR_FN_V1'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Built In Functions'
define view ZFNC_DDL as select distinct from makt as a
{
 key a.matnr as Mat_Num,
a.maktx as Mat_Desc,
right( a.maktx, 5 ) as Des_right, 
 
/*For Strings */
lpad( a.maktx, 6, 'xx' ) as Des_lpad, 
rpad( a.maktx, 6, 'y' ) as Des_rpad, 
ltrim( a.maktx, 't' ) as Des_ltrim, 
rtrim( a.maktx, 't' ) as Des_rtrim, 
replace( a.maktx, 'est','ough' ) as Des_replace, 
substring( a.maktx, 2, 1 ) as Des_substring, 
upper( a.maktx ) as Des_upper 
}

参考结果：

参考代码：

Example:
define view ZFLG_FNC_DDL as select distinct from sflight as a
{
key a.connid as Flg_Connid,
 
/*For Numericals */
lpad( a.connid, 6, '22' ) as Flg_lpad, 
rpad( a.connid, 6, '99' ) as Flg_rpad, 
ltrim( a.connid, '0' ) as Flg_ltrim,
a.fldate as Flg_Date, 
rtrim( a.fldate, '8' ) as Flg_rtrim
}

参考结果：

例子：将‘分钟’改为‘小时：分钟’
分钟：

@AbapCatalog.sqlViewName: 'ZFLIGHT_SCH_V'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Built In Functions'
define view ZFLG_FNC_DDL as select from spfli as a
left outer join sgeocity as b
    on a.cityfrom = b.city
{
 key b.city as Source,
 key b.city as Destination,
 key a.carrid as Flg_ID,
 key a.connid as Flg_Conn,
 a.fltime as Flg_Time
}

小时：分钟

@AbapCatalog.sqlViewName: 'ZFLIGHT_SCH_V'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Flight Schedule Details'
define view ZFLG_FNC_DDL as select from spfli as a
left outer join sgeocity as b
    on a.cityfrom = b.city
{
 key b.city as Source,
 key b.city as Destination,
 key a.carrid as Flg_ID,
 key a.connid as Flg_Conn,
    concat( concat(lpad ( ltrim ( cast( div(a.fltime, 60) as abap.char( 12 ) ), '0' ), 2, '0' ), ':' ) , 
    lpad ( ltrim ( cast( mod(a.fltime, 60) as abap.char( 12 ) ), '0'), 2, '0' ) ) as Flg_Time
}

单位转换函数

• 第一个参数是数值
• 第二个和第三个是源单位和目标单位

参考代码：

<code>@AbapCatalog.sqlViewName: 'ZUNIT_FN_V' 
@AbapCatalog.compiler.compareFilter: true 
@AbapCatalog.preserveKey: true 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
@EndUserText.label: 'Test Unit Funtions'
 define view ZUNIT_FN 
 as select distinct from mara as a
 {
    key a.matnr as Material,
     a.brgew as MatQuan,
     a.meins as SrcUnit,
     a.gewei as TgtUnit, 
     unit_conversion( quantity => brgew, 
                   source_unit => meins, 
                   target_unit => gewei ) as ConvF1
 } 
 where a.matnr > 'CH-6000'
   and a.matnr &lt; 'CH-6600';</code>

参考结果：

货币转换函数

• 第一个参数是金额
• 第二个和第三个是源货币和目标货币
• 第四个参数是汇率的日期

参考代码：

参考结果：

小数位转换函数

对于JPY, HUF, KRW, COP等特定货币来说，外部的显示和内部储存的形式是不所不同的。
在表TCURX中，JYP货币的小数位是0位，EUR没在这个表中，默认小数位是2位。

参考代码：

<code>@AbapCatalog.sqlViewName: 'ZDEC_SHFT_V'
 @AbapCatalog.compiler.compareFilter: true
 @AbapCatalog.preserveKey: false
 @AccessControl.authorizationCheck: #NOT_REQUIRED
 @EndUserText.label: 'Test Currency Conversion'
 define view ZDEC_SHFT
 with parameters p_amt : abap.curr( 5, 2 )
 as select distinct 
 from sflight as a{
     key a.carrid as FlgID,
     key a.connid as FlgConID,
     key a.fldate as FlgDat,
     a.currency,
     decimal_shift( amount => :p_amt, currency => a.currency ) as DEC_SHFT
 }</code>

参考结果：



从上面两幅图可以很明显的看出，输入21.34按照对应的货币有不同的解释。

日期和时间转换函数

日期

1. ADD DAYS(dats_add_days)
   3个参数，第1 个是要计算的基础日期，第2个是要加减的数字，第3个是用来错误处理的。
   
2. ADD MONTHS(dats_add_months)
   周上面dats_add_days相似的3个参数。
3. DAYS BETWEEN TWO DATES(dats_days_between)
   计划两个日期相差的天数
4. DATE VALIDATION(dats_is_valid)
   检验一个日期是否是有效日期，结果返回1或0。
5. DATS_TIMS_TO_TSTMP
   日期时间转为timestamp格式
6. TIMEZONE
   时区

参考代码：

<code>@AbapCatalog.sqlViewName: 'ZDT_TIME_FN_V'
 @AbapCatalog.compiler.compareFilter: true
 @AbapCatalog.preserveKey: true
 @AccessControl.authorizationCheck: #NOT_REQUIRED
 @EndUserText.label: 'Test Date and Time'
 define view ZDT_TIME_FN
 with parameters p_add_days  : abap.int4,  
                 p_add_months: abap.int4,
                 @Environment.systemField: #SYSTEM_DATE
                 p_curr_date : abap.dats 
 as select from sflight as a {
     key a.carrid as FlgId,
     key a.connid as FlgConnId,
     key a.fldate as FlgDate,
     dats_add_days     (a.fldate, :p_add_days  , 'INITIAL') as Added_DT,
     dats_add_months   (a.fldate, :p_add_months, 'NULL'   ) as Added_MT,
     dats_days_between (a.fldate, $parameters.p_curr_date ) as Days_BTW,
     dats_is_valid     (a.fldate)                           as Is_Valid                               
 } </code>

参考结果：

Case1


Case2:


Case3:

<code>@AbapCatalog.sqlViewName: 'ZDT_DT_TIME_FN_V'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Test Date and Time 2'
 define view ZDT_DT_TIME_FN
 with parameters  @Environment.systemField: #SYSTEM_DATE
                  p_curr_date : abap.dats 
 as select from sflconn as a{
     key a.agencynum as FlgAgy,
     key a.flconn    as FlgCon,
         a.arrtime   as FlgArr,
 
//Convert Arrival Time
 dats_tims_to_tstmp
 ($parameters.p_curr_date, 
  a.arrtime, 
  abap_system_timezone( $session.client,'NULL' ), 
  $session.client, 
  'NULL')  as Flg_Arr_Conv,              
 
abap_system_timezone( $session.client,'NULL' ) 
as MyTimeZone 
  }</code>

时间

• TIME_IS_VALID，检查参数是否是一个有效的时间，一个参数，字段类型应该而且必须是时间戳- DEC -长度15。
• UTC TIME，返回UTC时间，没有参数。
• ADD SECONDS，和上面计算时间的相似，可以正负计算
• SECONDS BETWEEN，同日期相似

参考代码：

@AbapCatalog.sqlViewName: 'ZTM_FNS_SQL_V'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Time Functions'
define view ZTM_FNS as select from zdt_concr_rept as a{
    key a.rpt_id as RPT_Comment,
    a.ztime as RPT_TMSP,
    tstmp_is_valid(a.ztime) as valid1,   
    tstmp_current_utctimestamp() as UTC_TM,
    tstmp_add_seconds(a.ztime, cast( 15 as abap.dec(15,0) ), 'INITIAL') as ADDED_TM,    
    //TESTING DIFFERENCE OF SECONDS
    tstmp_seconds_between(tstmp_current_utctimestamp(), a.ztime , 'FAIL') as difference 
}

参考结果：

weeks