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 **>=** operand2

These are really simple ones right? We also have **between**.

`operand1 `**between** operand2 **and** operand3

**Pattern 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 null**

### Arithmetic 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 DueAmount
```

### Aggregates 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 Currency

Below 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_space
```

### Decimal 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 Amount`

### Date 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 IsValidDate
```

### Nesting 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_Time
```

This 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.00
```

*Visit ABAP on HANA series for Tutorials on CDS, AMDP, Eclipse, and ALV IDA.*

**If you like the content, please subscribe…**