How to set null value in CDS views?

[vasuabes2011]

Hello folks, I am Vasu Sharma, SAP ABAP on HANA expert, have provided SAP solutions in various domains like manufacturing, aerospace industry, banking, finance and insurance.

With the help of this blog, you will be able to set null values in Core Data Service view.

 

Issue:

Recently, I came across with an interesting issue in a customized CDS view which was providing the data from few customized SAP S/4 HANA tables to the external system via interface.

• The SAP S/4 HANA customized table have a date field with some initial value ( 00000000 ) data in it.


• On providing the data of the customized table with help of CDS view, it was needed to convert the initial value of the date field to NULL value.

On doing the analysis, there is no direct function or keyword which translates the data into NULL value.

 

Hmm, what can be done then?

 

Don't worry, there is a solution below 😉

 

To achieve the solution, I used CDS function DATS_IS_VALID(date) and CASE expression without ELSE branch. Yes, without ELSE branch.

The CDS date function DATS_IS_VALID(date) determines the date passed in it contains a valid date in YYYYMMDD format. The result has the data type INT4.

A valid date produces the value 1 and all other input values produce the value 0. It considers the date 00000000 as invalid and hence produce value 0.

 

In CDS view:

 

 

All those values which are invalid will get converted to NULL values.

 

The recommended way to use CASE expression is with ELSE branch to avoid any null value, however, in our case, we needed to have null values so we omitted the else branch and it works perfectly fine. 🙂

 

Not only for date fields, you can set null value for any data type, make sure you don't get messed up the cases in CASE expression.

 

With this approach, find out those values which you want to transform in NULL value.

1. Identify the pattern of data which you want to convert to NULL value.


2. Use CASE expression without ELSE branch and all your target data which you want to convert into NULL value must not fall under 'WHEN' conditions.

 

Thanks for reading it. Have a nice day!