Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
lenapadeken
Product and Topic Expert
Product and Topic Expert
2,828
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.






Keep in mind that ABAP does not have null values. Rather, null values that are assigned to an ABAP data object in ABAP SQL, Native SQL, or AMDP are converted to type-dependent initial values. Read more here.

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😞



























































CARRID CONNID FLDATE PRICE CURRENCY PLANETYPE BOOK_A_SEAT
LH 0401 2023-03-14 668.2 EUR 767-200
LH 0402 2023-03-10 668.2 EUR A380-800 yes
LH 2402 2023-03-15 244.2 EUR A380-800 yes
LH 2407 2023-03-15 244.2 EUR A320-200
LH 0400 2023-03-15 808.52 EUR A340-600

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:









































CARRID DISTID DISTANCE NULL
AA MI Miles
AA MI Miles
AC
AF
AZ MI Miles

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.


















































































Category Syntax Argument True/False
Numeric function ROUND( sql_exp, pos ) pos True
String function LIKE_REGEXPR( pcre = pcre, value = sql_exp[, case_sensitive = case] ) pcre
case
True
LOCATE( sql_exp, sub[ ... ] ) sub True
LOCATE_REGEXPR( pcre = pcre, value = sql_exp[, case_sensitive = case][ ... ] ) pcre
case
True
LOCATE_REGEXPR_AFTER( pcre = pcre, value = sql_exp[, case_sensitive = case][ ... ] ) pcre
case
True
OCCURRENCES_REGEXPR( pcre = pcre, value = sql_exp[, case_sensitive = case] ) pcre
case
True
REPLACE_REGEXPR( pcre = pcre, value = sql_exp1, with = sql_exp2[, case_sensitive = case][ ... ] ) pcre
case
True
SUBSTRING_REGEXPR_AFTER( pcre = pcre, value = sql_exp[ ... ][, case_sensitive = case][ ... ] ) pcre
case
True
Unit conversion UNIT_CONVERSION( quantity = a1, source_unit = a2, target_unit = a3[, client = a4 ][ ... ] ) a2
a3
a4
False
Currency conversion CURRENCY_CONVERSION( amount = a1, source_currency = a2, target_currency = a3[, exchange_rate_date = ... a2
a3
a4
a6
False
Window function FIRST_VALUE( col|sql_null ) sql_null True
LAST_VALUE( col|sql_null ) sql_null True
Relational expression BETWEEN operand1 AND operand2 operand1
operand2
True

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.




















Code Error type Error message

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 )

Static It is not possible to derive a type for the NULL value from its context.

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

Dynamic A catchable exception of class CX_SY_OPEN_SQL_DB is raised.

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:

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 (lenapadeken) for similar blog posts.
4 Comments
Labels in this area