In this post, you will learn about various expressions and operations in CDS view.
So far, we have looked at creating a simple CDS view, CDS view with associations, parameters etc. Read earlier posts in this series at Exploring ABAP on HANA.
This post will focus bit more on syntax part rather than concepts as concepts here are either self explanatory or have been covered in earlier posts.
Projection List
Fields of data source
Field can be a key field or a non-key field. Alias can be used. If alias is not used then the field name itself becomes the final column name.
Literals
These are constants of numeric or characters type and simply put the constant value for all rows in the output. Eg. ‘12345’, ‘CDS’. It is mandatory to specify alias name for literals in the field list.
Session Variables
Below session variables can be used with $session.variable_name
| Variable | Usage |
| user | Current user name : SY-UNAME |
| client | Current client : SY-MANDT |
| system_language | Current language : SY-LANGU |
| system_date | Current system date : SY-DATUM (Available from 7.51) |
Case Statement and Conditions
Simple case – Case statement on single variable similar to ABAP.
Complex / Searched case -This has multiple conditions in each case and it can have conditions other than ‘equal to’ ( = ).
Consider below Marvels Movie table for this example..
Suppose, we want to produce Phase as First, Second, Third, Fourth instead of Phase 1, Phase 2 and so on, we can use Simple Case statement.
Similarly, if we want to call a movie Hit, Flop etc based on the opening collection, we can use Complex Case statement.
Projection List
Fields from projection list can be accessed using $projection. You can use this in Associations.
Parameters
In a CDS view with a parameter, a parameter can be addressed with a colon ( : ) or with $parameters.
Conditions
Regular Comparisons
operand1 = operand2
operand1 <> operand2
operand1 < operand2
operand1 > operand2
operand1 <= operand2
operand1 >= operand2These are really simple ones right? We also have between.
operand1 between operand2 and operand3Pattern comparisons
This is similar to contains string or the like operation that we perform in Open SQL.
What if the data itself has % or _ which you want to search?
Then we can use Escape Character. Suppose movie name was ‘Avenger% 1’ and we want to search with pattern Avenger%, then we can use the escape character in the pattern string. SAP recommends to use # as escape character.
when movie like 'Avenger#%%' escape '#'Check for null values
operand1 is null
operand2 is not nullArithmetic Operations
We can have addition, subtraction, multiplication, division and in addition to these a negation operator.
case indicator
when 'Credit' then amount
when 'Debit' then -amount
end as DueAmountAggregates Functions
Aggregate functions evaluate multiple records from the source and return aggregations such as total, count.
MAX, MIN, AVG, SUM, COUNT are the aggregate functions available. These are pretty self explanatory, so I am going to mention only one example.
Group By clause is mandatory when using aggregate functions. Having clause can be added to filter data based on aggregation.
As phase 4 collection does not match the condition, it is not shown.
Type Casting
We have seen this in the currency and unit conversion post.
cast( 'EUR' as abap.cuky) as CurrencyBelow are the types that can be used.
| Data Type | Description |
| abap.char( len ) | Character with length specification |
| abap.clnt[(3)] | Client |
| abap.cuky( len ) | Currency Key |
| abap.curr(len,decimals) | CURR with length len and decimals decimal places |
| abap.dats[(8)] | Date |
| abap.dec(len,decimals) | DEC with length len and decimals decimal places |
| abap.fltp[(16,16)] | Float |
| abap.int1[(3)] | INT1 |
| abap.int2[(5)] | INT2 |
| abap.int4[(10)] | INT4 |
| abap.int8[(19)] | INT8 |
| abap.lang[(1)] | Language |
| 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)] | Time |
| abap.unit( len ) | Unit |
Numeric Functions
Below numeric functions are possible.
| FUNCTION | OUTPUT |
| ABS(arg) | Absolute amount |
| CEIL(arg) | Round Up |
| ROUND(arg, pos) | Rounding with number of decimal specification |
| FLOOR(arg) | Round Down |
| DIV(arg1, arg2) | Quotient |
| DIVISION(arg1, arg2, dec) | The result is rounded to dec decimal places. |
| MOD(arg1, arg2) | Remainder |
String Functions
Operations like concatenation, Substring, replace, length, left substring, right substring removing right or left blanks or zeros etc. are possible.
concat( char1, char2 ) as concat,
length( char1 ) as length,
left( char1, 3 ) as left,
lower( char2 ) as lower,
upper( char2 ) as upper,
right( char1, 3 ) as right,
substring( char1, 3, 3 ) as substring,
replace( char1, ',' , '.' ) as replace,
concat_with_space( char1, char2, 1 ) as concat_with_spaceDecimal Shift
This can be used to get the amount as per the currencies, specially the ones which have other than 2 decimal places.
decimal_shift( amount => amount, currency => currency ) as AmountDate Functions
Functions like add days, add months, days between dates, and date validation can be used in the CDS.
dats_add_days (releasedon, 14, 'INITIAL') as DateAfter2Weeks,
dats_add_months (releasedon, 3, 'NULL' ) as Dateafter3Months,
dats_days_between (releasedon, :iv_curr_date ) as DaysSinceTheRelease,
dats_is_valid (releasedon) as IsValidDateNesting Functions
It is also possible to use functions within functions like below which converts time from minutes to format HH:MM.
concat( concat(lpad ( ltrim ( cast( div(fltime, 60) as abap.char( 12 ) ), '0' ), 2, '0' ), ':' ) ,
lpad ( ltrim ( cast( mod(fltime, 60) as abap.char( 12 ) ), '0'), 2, '0' ) ) as Flight_TimeThis is probably a more complex example which
- gets the number of hours by dividing the time by 60
- casts the results as character
- removes leading zeros if any
- adds leading zero up to 2 places (so that 1 shows as 01)
- repeats this process for minutes, just this time uses mod to get remaining minutes
- then concatenates HH and : into say HH: and then concatenates it with MM to get HH:MM
The point here is not to understand all this right away, but to know that such complex expressions are possible.
I have not given examples of actual CDS code and outputs here as so far, I am sure you are comfortable with CDS views and can try these out yourselves.
However, if you need such examples, do let me know in the comment section and I will post dedicate article on required section.
Reference Code
ZJP_CDS_EXPRESSIONS
@AbapCatalog.sqlViewName: 'ZJP_SQL_EXP'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Various Expressions'
define view zjp_cds_expressions as select from sflight {
key carrid as Carrid, //Key Field
key connid as Connid, //Key Field
key fldate as Fldate, //Key Field
seatsmax as Seatsmax, //Non-Key Field
seatsocc as Seatsocc, //Non-Key Field
'Flight' as Title, //Literal - Text
10 as Num1, //Literal - Number
$session.user as UserName,
$session.client as Client,
$session.system_language as SystemLanguage
//$session.system_date as SystemDate,
}ZJP_10_CDS_EXPRESSIONS
@AbapCatalog.sqlViewName: 'ZJP_10_EXP'
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'CDS Expressions and Operations'
define view ZJP_10_CDS_EXPRESSIONS
as select from zmmovie
{
key movie as Movie, //Key Field
phase as Phase_Orig,
case phase //Simple Case
when 'Phase 1' then 'First'
when 'Phase 2' then 'Second'
when 'Phase 3' then 'Third'
else 'Fourth'
end as Phase_New,
case //Complex Case
when openingcol > 050000000.00 and openingcol <= 100000000.00
then 'Hit'
when openingcol > 100000000.00 and openingcol <= 250000000.00
then 'Superhit'
when openingcol > 250000000.00
then 'Blockbuster'
else 'Flop'
end as Verdict
}ZJP_11_CDS_EXPRESSIONS
@AbapCatalog.sqlViewName: 'ZJP_SQL_AGGR'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Aggregates'
define view ZJP_11_CDS_EXPRESSIONS
as select from zmmovie
{
phase as Phase,
@Semantics.amount.currencyCode : 'Currency'
sum(openingcol) as TotalOpenCollection,
@Semantics.currencyCode: true
currency as Currency
} group by phase, currency
having sum(openingcol) > 650000000.00Visit ABAP on HANA series for Tutorials on CDS, AMDP, Eclipse, and ALV IDA.
If you like the content, please subscribe…
Discovering ABAP 
