A null value is a special value returned by a database to indicate an undefined value or result. There are no special null values in ABAP, and it’s not easy to add such a feature to the language these days. The value range of a data type takes up the entire memory space of its data object. Imagine, for example, the simple one byte integer type INT1 from the ABAP Dictionary, corresponding to b in ABAP: The value range is 0 to 255, which is exactly the range you can express with bits of 1 byte (hexadecimal 00 to FF). There’s no space left for a null value and no value left to represent a null value.

When a null value is read with ABAP SQL from the database, it’s simply converted to the type-specific initial value of the ABAP target field, which is 0 for numeric types and blank for character types. So there’s no way to tell if a null value was read by a simple comparison. For a long time, the only way to handle null values was the relational expression IS NULL and, more recently, null indicators.

In fact, in the early ABAP days, this was never seen as a problem because in the ABAP world, null values only appeared in the database in exceptional cases:

• In ABAP SQL, write accesses to database tables do not normally produce null values.
• Null values can be produced in DDIC database tables when new columns are appended to populated tables. However, this can be overridden with a flag for initial values.

In terms of read accesses, it was only with the introduction of outer joins in ABAP Release 4.0 that null values began to appear in result sets. From then on, as more and more SQL expressions and functions were added, null values appeared as results of these expressions and functions. A prominent example being case distinctions: If you omit the ELSE statement, the expression will automatically produce the null value as a result. Other examples are functions that return null values when an exception occurs. For these reasons, null values are becoming more common in ABAP SQL and there is a need to improve the handling of null values.

In addition to the relational expression IS NULL and the null indicators mentioned above, there is a new way of explicitly expressing a null value in ABAP SQL, namely the expression NULL.

While ABAP CDS introduced ELSE NULL with ABAP Release 7.89, SAP BTP ABAP Environment 2208 (see feature matrix of all CDS DDL features), which allows you to specify a null value after ELSE of a CASE expression, ABAP SQL goes a step further by introducing a new SQL expression NULL that can be used in many more operand positions of ABAP SQL. The special SQL expression NULL is available since ABAP Release 7.83, SAP BTP ABAP Environment 2102.

This blog gives a brief introduction to the null expression, and since NULL can be used almost anywhere in the ABAP SQL environment, we’ve tested every position and summarized our findings and possible pitfalls.

Introduction

The operand NULL represents the null value and can be used at many operand positions of SQL expressions. A famous example is the case distinction introduced above.

Thus, the expression NULL is an option to handle null values in ABAP SQL. Other options are the relational expression IS NULL and null indicators. The difference between NULL and IS NULL is explained below. One important aspect of using NULL is that it must be possible to derive a type (see Pitfalls).

Introducing NULL, the goal was to allow NULL as a constant column value in the field list. In the end, NULL was generally allowed in ABAP SQL. This makes it possible to set an entire expression to null by specifically using NULL for one of its operands depending on the condition. Possible use cases are explained below.

Use case

Two possible use cases of the null expression are ELSE NULL and THEN NULL, both of which are part of case distinctions. Another example of case distinctions is given in the Scope section to demonstrate the operand positions in which NULL can be used. The following sample code shows an example of all flights in March 2023 with the airline code LH. There’s a case distinction included to identify flights with available first class seats.

METHOD main.
    SELECT FROM sflight
           FIELDS carrid,
                  connid,
                  fldate,
                  price,
                  currency,
                  planetype,
                  CASE
                     WHEN seatsocc_f < seatsmax_f THEN 'yes'
                     WHEN seatsocc_f = seatsmax_f THEN CAST( NULL AS CHAR( 1 ) )
                  END AS book_a_seat
           WHERE fldate BETWEEN datn`20230301` AND datn`20230331`
              AND carrid = 'LH'
           INTO TABLE @FINAL(result) INDICATORS NULL STRUCTURE null_ind.

    cl_demo_output=>display( result ).
ENDMETHOD.

(By the way, do you recognize the typed literals in the example? Check out this blog post.)

The query returns the following (excluding NULL_IND):

The use case for NULL here is that you’re mainly interested in the available seats, but would still like to know about other flights, perhaps for planning a flight in the coming months. Everything about NULL and the null indicator is explained below.

Scope

The following example shows where NULL can be used in a simple case distinction. You can find an example like this in the ABAP Keyword Documentation.

METHOD main.
    SELECT FROM scarr
             LEFT OUTER JOIN spfli
               ON scarr~carrid = spfli~carrid
           FIELDS scarr~carrid,
                  distid,
                  CASE distid
                     WHEN 'MI' THEN 'Miles'
                     WHEN 'KM' THEN 'Kilometers'
                     ELSE NULL
                  END AS distance,
                  CASE distid
                     WHEN 'MI' THEN NULL
                     WHEN NULL THEN 'Kilometers'
                     ELSE NULL
                  END AS null
           INTO TABLE @FINAL(result)
           UP TO 5 ROWS.

    cl_demo_output=>display( result ).
ENDMETHOD.

In this example, two simple case distinctions are specified that both contain NULL. NULL can be used explicitly in the operand position after ELSE, but also after WHEN and THEN. The result table looks like this:

When looking at the result table, you cannot see whether null values are present or not. This is because null values are converted to initial values and are represented by blanks, for example. Null indicators can be used to identify null values:

INTO TABLE @FINAL(result) INDICATORS NULL STRUCTURE null_ind

Using null indicators, columns of the result set containing the null value can be determined. In this example, a structured indicator is used and each component of the structure is of type x with length 1. A component value of hexadecimal 1 denotes a null value in the respective column of the result set. The result table now looks like this:

Result table with a null indicator

The result table emphasizes that the columns DISTID, DISTANCE, and NULL return the null value for all comparisons that are not true. Additionally, the column NULL returns an unknown result for comparisons with the null value. While NULL is passed to the result table, null values are converted to initial values.

Findings

Basically, NULL can be used at any operand position of an SQL expression. The following table marks exceptions. The column True/False indicates if NULL can be used (True) or if NULL cannot be used (False) at a specific operand position.

Most positive exceptions occur for string functions as valid argument type for pcre, sub, and case. The window functions are mentioned here because only columns were allowed as argument before NULL was introduced.

Pitfalls

As mentioned above, the two most important take-aways for using NULL are to remember that it must be possible to derive a type and that the null expression can be specified at almost all SQL expression positions with a few exceptions. If NULL is used in a relational expression, the result is never true but unknown. Unknown is similar to false but the behavior is different for AND, OR, and NOT. Additionally, another possible pitfall comes to light when testing error handling with, for example, the UNIT_CONVERSION() function.

unit_conversion(
  quantity = dec3,
  source_unit = cast( null as unit ),
  target_unit = unit`KM`,
  on_error = @sql_unit_conversion=>c_on_error-set_to_null )

unit_conversion(
  quantity = dec3,
  source_unit = unit`MI`,
  target_unit = null,
  on_error = @sql_unit_conversion=>c_on_error-set_to_null )

Based on the first example, you can assume that you need to specify a length for types that do not have a fixed length. For example, writing CAST( NULL AS CHAR( 10 ) ). This is true and you need to specify the length for all types with a variable length for casts with NULL. The second example shows the dynamic error handling of NULL. Further, as introduced above, the expression NULL looks similar to the relational expression IS NULL. Important to note is that while NULL is an expression, IS NULL is a relational expression with predicate syntax.

Further Information

The SQL expression NULL handles null values and can be specified at most operand positions in an SQL expression. NULL is type-dependent and needs a specified length for types with variable lengths in a cast expression. Do not confuse the relational expression with the predicate syntax IS NULL with the expression NULL. Keeping everything in mind, you can start using NULL. Some further references are listed below:

• Feature Matrix: Data Modeling with ABAP Core Data Services (Blog)
• SQL Expression NULL (ABAP Keyword Documentation)
• SQL Expression NULL – Example (ABAP Keyword Documentation)

You should now know how to use the SQL expression NULL in your projects. Use NULL for null handling in ABAP SQL. The examples given in this blog are intended for demonstration purposes only. Are you excited to use the special null expression? Write your thoughts in the comment section. Don’t miss out on new language elements and follow my profile (Lena Padeken) for similar blog posts.