You think there is not ‘Null’ value on SAP but you are wrong. It exists!

SAP does not have a predefined type as ‘Null‘. However, there is a comparison operator in OpenSQL and CDS queries. You can use ‘is null‘ or ‘is not null‘. So, if SAP does not have ‘Null‘, what are those meanings?

I have a table:

if I make a query like following

CARRID	CONNID	COUNTRYFR	CITYFROM	AIRFROM	COUNTRYTO
AA	17		NEW YORK	JFK
AA	64		SAN FRANSICO	SFO
AC	820	DE	FRANKFURT/MAIN	FRA	CA
AF	820	DE	FRANKFURT/MAIN	FRA	MQ

If I make a query like by changing only where condition:

SELECT COUNT( * )
FROM spfli
WHERE countryfr = ''
INTO @data(count_of_initials).

* result:
*> 2

SELECT COUNT( * )
FROM spfli
WHERE countryfr is initial
INTO @data(count_of_initials).

*Result will be same:
*> 2

If you try searching by null values:

SELECT COUNT( * )
FROM spfli
WHERE countryfr is null
INTO @data(count_of_initials).

* Result:
*> 0

It cannot find any data. Do not forget SAP does not have ‘Null’ values store null values on tables. No value equals empty in SAP tables. If a field is empty in a table, its value is initial value. Initial value is changeable. If it is integer then 0 or if it is char then ” etc.

What if I make a query like:

SELECT carrid, connid, countryfr, landx AS country_desc
    FROM spfli
    LEFT OUTER JOIN t005t ON t005t~land1 = spfli~countryfr
                         AND t005t~spras = 'E'
    WHERE t005t~land1 IS NULL
    INTO TABLE @DATA(flights).

Result is:

CARRID	CONNID	COUNTRYFR	COUNTRY_DESC
AA	17
AA	64

How did I get a result if there was nothing as null?

Null is a little bit different on SAP. It can only be usable on joins. It means it cannot find any relation and only fetched left table. So there is no right table. Right tables fields are null in this case.

So above query fetches lines which has not find any relation on T005T. As you can see, AA – 17 and AA – 64 lines do not have any value on COUNTRYFR field. So, there is no possibility to make a relation with T005T because T005T table does no contain any data as LAND1 is initial.

This query means only fetch all data from SPFLI and COUNTRYFR field does not exists in T005T.

Same logic also valid on CDS Queries. I realized this property on CDS.

define view zoz_cds_null_test as select from ekbe
    left outer join matdoc on matdoc.mblnr = ekbe.belnr
{
 ekbe.ebeln,
 ekbe.ebelp,
 ekbe.belnr,
 matdoc.mblnr   
}
where matdoc.mblnr is null

This query will fetch BELNR fields which are only exists on EKBE but does not exists on MATDOC.

You may try your own system to see.