Special ABAP SQL expression NULL

[lenapadeken]

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:

• 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 (lenapadeken) for similar blog posts.