Skip to Content

Let’s Start an Experiment

In the last instalments of this weblog series I dealt with semantic data models, SERM and SAP Data Modeller. Now I cover an completely different topic. Let’s start with an experiment and create a transparent table:

image

Then we use transaction se16 to enter some data:

image

Now we add another column and activate the transparent table:

image

Now here’s an effect that might be surprising to you: every search using se16 for a CONN_ID value won’t find anything:

image

But what went wrong? Let’s switch on the flag “Initial Values”:

image

And finally after activation we get the expected result:

image

What happened? When we appended the column the new fields have been filled with NULL values which didn’t match the expression SELECT COUNT() FROM ZTEST WHERE CONN_ID EQ SPACE. Let’s have a look at the F1-documentation of the “Initial Values” in detail:

Indicator that NOT NULL is forced for this field

Select this flag if a field to be inserted in the database is to be filled with initial values. The initial value used depends on the data type of the field. Please note that fields in the database for which the this flag is not set can also be filled with initial values. When you create a table, all fields of the table can be defined as NOT NULL and filled with an initial value. The same applies when converting the table. Only when new fields are added or inserted, are these filled with initial values. An exception is key fields. These are always filled automatically with initial values.

Restrictions and notes:

  • The initial value cannot be set for fields of data types LCHR, LRAW, and RAW. If the field length is greater than 32, the initial flag cannot be set for fields of data type NUMC.
  • If a new field is inserted in the table and the initial flag is set, the complete table is scanned on activation and an UPDATE is made to the new field. This can be very time-consuming.
  • If the initial flag is set for an included structure, this means that the attributes from the structure are transferred. That is, exactly those fields which are marked as initial in the definition have this attribute in the table as well.

What are NULL Values?

There are different semantics for NULL values in database tables:

  • unknown value (there is value but we don’t know it),
  • not existing value (we can’t apply the attribute),
  • missing information (there may be a value but we don’t know it).

SQL standard defines some rules for NULL values:

  1. You can’t insert a NULL value in a column that that is defined NOT NULL,
  2. The result of a comparison between two NULL values is not true – you have to use the IS NOT NULL and IS NULL.
  3. If a column contains NULL values it will be ignored when using aggregations: MAX, AVG and SUM.
  4. When doing grouping using GROUP BY then there are special rows for the results.
  5. If a table contains NULL values in joins there apply rules for outer joins.

In fact above rules apply for ABAP as well. I suggest reading details in transaction ABAPHELP.

What are NULL Values Used For?

We already saw how to create NULL values in a database table by appending a row with the flag “initial values” set off. But there is another possibility: insert a new row using a view that doesn’t affect a column with the flag “initial values” switched off. But even if you don’t have NULL values in two transparent tables it is easy to create NULL values in a left outer join which is left as an easy exercise to the reader.

In fact NULL values are not very useful because there is no NULL in ABAP and is difficult to set a database field to NULL. Usually we use working areas (resp. internal tables) to update a transparent table – but if we select a row into a working area the NULL value will be converted to an initial value and after the update the NULL value is lost.

But there is one interesting application for NULL values. If we need a post processing after appending a row to a transparent table to calculate values for new fields it is very useful to be able to distinguish between new fields and already calculated fields with initial values.

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. David Halitsky
    … so all I can say is “heh heh heh” …

    Sorry – gotta run – I think my tongue is gonna need stitches, actually.

    But before I go, I seriously wanna say: “great blog!” – I’ve already placed its URL in my good_sdn_links folder.

    What do you want to bet on how many questions involving NULL are in ABAP General ????

    regards
    djh

    (0) 
  2. Tobias Trapp Post author
    This is really strange. But it should be possible to   get more information. Can you write a SELECT statement that uses IS NULL?

    Regards,
    Tobias Trapp

    (0) 
  3. Tobias Trapp Post author
    Sorry I forgot, can you send me the description of the database object? You can find it in the se80 -> utilities -> database utilities or report RUTDBCHK .

    Regards,
    Tobias Trapp

    (0) 
  4. Tobias Trapp Post author
    Sorry, but I have really no idea why you can’t reproduce it – NULL values are a quite common thing in ABAP and I found nothing in the OSS that could explain it.

    Nevertheless, I’d like to find out what went wrong. Perhaps you can send me some hardcopies of your steps by mail, you’ll find it in my business card.

    Regards
    Tobias Trapp

    (0) 
    1. Former Member
      hi,

      I did this experiments some time ago and as far as I  remember the NULL-is initial-” effects depend on the database in use (which is strange given that we have a DB abstraction layer).

      maybe someone from WDF could enlighten us here.

      regards,
      anton

      (0) 
      1. Former Member
        could it be that

        SELECT COUNT() FROM ZTEST WHERE CONN_ID IS NULL OR CONN_ID LE ‘0’.

        yields a correct result in any case?

        just digging in memories,
        anton

        (0) 
        1. Tobias Trapp Post author
          Richard Heilman mailed me the database object (report RUTDBCHK or se80 -> utilities). And here we can see that the DDIC defined the column as *not* initial but in the database object it is initial and the initial value is ‘0000’.

          So the result of the SELECT is the “correct” database result. But there is a contradiction between both objects and at the moment we want to find out why that happend. In fact I don’t know this behaviour –  all databases I used so far (Oracle, DB2 and MaxDB) I got the expected effect.

          Cheers,
          Tobias

          (0) 

Leave a Reply