Filtering Null Values of Type DATS using SAP Open SQL

SAP logo

In October, I started a new phase in my career of twenty-five years as an IT professional: SAP software engineer.

I had worked with SAP in some capacity over the years, but those experiences mostly revolved around connecting APIs to SAP endpoints or querying the SAP database directly using a SQL client like Oracle Developer. I had wanted to learn the inner workings of SAP for many years, but never really had the opportunity until now to devote time to it. I did take a beginning ABAP Objects class back around 2006, but never was in a position to use it.

In my new position, I am learning SAP and its programming language, ABAP, and its implementation of SQL: Open SQL.

If you are familiar with COBOL, the ABAP programming language may not look all that foreign to you. There are plenty of resources that you can use to learn ABAP, even if you don’t have access to an instance of SAP. Even so, I would say that the learning curve when beginning is steeper than it is with more modern programming languages like Python or Ruby.

Open SQL code is embedded quite naturally into the ABAP code, and uses syntax that is familiar to anyone who has used SQL before – for the most part. There are some key differences, and one I’m working on today has to do with null values in the DATS data type, which is for dates. The data type is structured like CHAR(8).

In a standard SQL query, you could filter out null values like this:

FROM ztable INTO #tmp_ztable
WHERE some_date_column IS NOT NULL;

In Open SQL, this does not work. A similar query in Open SQL might look like this:

FROM ztable INTO @DATA(lt_ztable)
WHERE some_date_column NE 00000000.

(The “NE” operator is for “not equal to” and is interchangeable with “<>”.)

Notice that there are no single quotes around the eight zeros in the WHERE clause – it is not a string of eight zeros, which would be represented by ‘00000000’.

Why does SAP do this? I don’t know. I’m sure there’s a reason that I’ll find out at some point, but I haven’t found it yet. There’s much more to learn!