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.