SAP ABAP CDS Arithmetic Functions – Ceil, Floor, Div (Integer), Division (Dec), Mod, Round & Aggregate Expressions – MAX, MIN, AVG, SUM, COUNT, Group By Clause, HAVING Clause

SAP ABAP CDS Arithmetic Functions

Ceil, Floor, Div (Integer), Division (Dec), Mod, Round are basic Arithmetic Functions or expressions 

@AbapCatalog.sqlViewName: 'ZDEMO_ARTH_V'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Built In Arithmetic Functions'
define view ZFLIGHT_ARTH 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
 }

Ceil is 26 for a number 25.3 while Floor is 25 for the same number 25.3.

See More: CDS String Functions(Concat, Instr, Left / Right, Length, Lpad / Rpad, Ltrim / Rtrim ,Replace)

Aggregate Expressions are commonly used Arithmetic Functions

MAX
MIN
AVG
SUM
COUNT

Note:Require a GROUP BY clause when aggregate or arithmetic functions are used along with other elements in the projection list

COUNT

You can calculate the number of rows in the result set using COUNT.

@AbapCatalog.sqlViewName:'S4HCDS_SEL_VD16'
defineviews4hcds_sel_v16as
select from sbook
{
count( * ) as booked_flights
}
where carrid= 'LH' and connid= '0400'

MIN, MAX, SUM

You can calculate the minimum or maximum value in a column.
What is the price range of the flights?

@AbapCatalog.sqlViewName:'S4HCDS_SEL_VD17'
defineviews4hcds_sel_v17 as select from sflight
{
min( price) as Min_Price,
max( price) as Max_Price,
sum( price) as Total_Price
}
where carrid= 'AA' and connid= '0017'

Group By Clause

  • A table can be divided into (disjoint) groups of rows.
  • A group is represented in the query result by a single row.
  • Aggregate expressions will be evaluated separately for each group.
  • Columns outside of aggregate functions must be listed in GROUP BY clause.
@AbapCatalog.sqlViewName:'S4HCDS_SEL_VD18'
defineviews4hcds_sel_v18as select from sflight
{
  planetype,
  count( *) as Count_flights
}
where planetype= 'A340-600' or
      planetype= 'A319-100'
group by planetype

Having Condition

  • HAVING condition is evaluated after the group by clause is evaluated.
  • HAVINGcondition can only be specified together with GROUP BY.
  • CDS Arithmetic Functions can be specified in the HAVING condition.
@AbapCatalog.sqlViewName:'S4HCDS_SEL_VD19'
define views4hcds_sel_v19as select from sflight
{
  carrid,
  fldate,
  count(*) as flight_count
}
group by carrid, fldate
having count(*) > 2

 

Advertisement

Leave a Comment