Much Awaited Sequel on CDS Operations and Expressions is finally here.
Not much time has passed since we gave you a Heads Up and introduced you to Basic Expressions & Operations Available for CDS View. To forget is Human. Check this out before you go ahead.
Basic Operations & Operations – Part I
If you are ready, let us continue from where we left.
1. Arithmetic Expression
Straight Forward. Addition/Subtraction/Multiplication/Division. Let’s recall BODMAS rule which you learned in your elementary school. Period.S o WHAT’S new ???
We have a prefix operator ‘-‘, which can be attached to a numeric field and it translates to
‘Field Value’ * ‘-1’ = -‘Field Value’
2. Aggregate Expressions
Most IMPORTANT concept probably of the entire HANA-ABAP.
It assumes a lot of importance in wake of the fact that HANA DB has Column Based Storage of Data in Tables.
Let’s check what is there in store for us in this part of the story.
| Aggregate Function | Usage |
| MAX | Finds the Greatest value of a particular field |
| MIN | Finds the Least value of a particular field of operand |
| AVG | Finds the Average value of a particular field |
| SUM | Sum of a particular field |
| COUNT | If DISTINCT operand is specified, the number of distinct values of operand; if * is specified, the number of rows in the results set. |
This requires us to do some programming to understand in depth and convincingly.
@AbapCatalog.sqlViewName: 'ZFLG_CDS_OP_V'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Operations'
define view ZFLG_CDS_OP
as select from sflight as a
{
key a.carrid as FlgCarr,
max( a.price ) as MaxPrice,
min( a.price ) as MinPrice,
avg( a.price ) as AvgPrice,
sum( a.price ) as SumPrice,
count( * ) as TotalCount
}OH WAIT !! I have a committed some crime here.
Yupp, no excuse. There needs to be Community Service. What is it? Discuss Group By.
GROUP BY
We at SAPSPOT always believe in unconventional explanations. Let’s understand with the help of an unassuming example.
Eg. Say there is a group of animals. Need to have a way to differentiate them. What you do? Group them. Like, Flock of Birds, Herd of Zebra, Group of Female Lions – a PRIDE, Group of Male lions – a Coalition.
Effectively you put things of similar objects together, so that we can easily take a count, find the extremes like minimum or maximum, look for the median like average and so on.
Hope you have some idea now, why we need Group By.
Back to example. I modified adding group by ‘CARRID’. Meaning we shall be finding all the Aggregate Function value for each ‘CARRID’.
@AbapCatalog.sqlViewName: 'ZFLG_CDS_OP_V'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Operations'
define view ZFLG_CDS_OP
as select from sflight as a
{
key a.carrid as FlgCarr,
max( a.price ) as MaxPrice,
min( a.price ) as MinPrice,
avg( a.price ) as AvgPrice,
sum( a.price ) as SumPrice,
count( * ) as TotalCount
} group by a.carridNow let’s see the initial output:
Total Count for Flight Carrier ‘AA’ is 26 and ‘DL’ is 39.
NOTE: The format of Average Price is converted in Decimal and we had no control of how exactly we could display. But, we can. That’s in next section. Type Casting.
Important Considerations while Using Group By:
i) All the Key fields defined in the CDS needs to be part of Group By Clause.
I included another field ‘CONNID’ as a key field and see the difference.
@AbapCatalog.sqlViewName: 'ZFLG_CDS_OP_V'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Operations'
define view ZFLG_CDS_OP
as select from sflight as a
{
key a.carrid as FlgCarr,
max( a.price ) as MaxPrice,
min( a.price ) as MinPrice,
avg( a.price ) as AvgPrice,
sum( a.price ) as SumPrice,
count( * ) as TotalCount
} group by a.carrid, a.connid
We see that now the aggregate functions behave differently, because all calculations are done by first picking Carrier ID and then Connection ID. Please note the total count is 26 in both cases for Flight Carrier ‘AA’. Similarly the total count is 39 for Flight Carrier ‘DL’.
ii) The Group By can be enhanced by adding HAVING.
@AbapCatalog.sqlViewName: 'ZFLG_CDS_OP_V'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Operations'
define view ZFLG_CDS_OP
as select from sflight as a
{
key a.carrid as FlgCarr,
key a.connid as FlgConn,
max( a.price ) as MaxPrice,
min( a.price ) as MinPrice,
avg( a.price ) as AvgPrice,
sum( a.price ) as SumPrice,
count( * ) as TotalCount
} group by a.carrid, a.connid
having a.carrid = 'AA'
3. Type Casting Expressions
Another important concept that is quite handy while manipulating data is Type Casting. The original data type can be changed to another (infact forced to change). The following is the curated list (out of a longer list) that can be useful:
| Data Type | Description |
| abap.char( len ) | CHAR with length len |
| abap.clnt[(3)] | CLNT |
| abap.cuky( len ) | CHAR with length len |
| abap.curr(len,decimals) | CURR with length len and decimals decimal places |
| abap.dats[(8)] | DATS |
| abap.dec(len,decimals) | DEC with length len and decimals decimal places |
| abap.fltp[(16,16)] | FLTP |
| abap.int1[(3)] | INT1 |
| abap.int2[(5)] | INT2 |
| abap.int4[(10)] | INT4 |
| abap.int8[(19)] | INT8 |
| abap.lang[(1)] | LANG |
| abap.numc( len ) | NUMC with length len |
| abap.quan(len,decimals) | QUAN with length len with decimals decimal places |
| abap.raw(len) | RAW |
| abap.sstring(len) | SSTRING |
| abap.tims[(6)] | TIMS |
| abap.unit( len ) | CHAR with length len |
Code: ??
Knowingly, we have not provided the program sample. For now Try it out by yourself !!! If you have any issues, please put down your queries. Treat this as an Assignment from SAPSPOT.
