Data Modelling and Database Design in ABAP – Part 4
Tobias Trapp
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:
Then we use transaction se16 to enter some data:
Now we add another column and activate the transparent table:
Now here’s an effect that might be surprising to you: every search using se16 for a CONN_ID value won’t find anything:
But what went wrong? Let’s switch on the flag “Initial Values”:
And finally after activation we get the expected result:
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:
- You can’t insert a NULL value in a column that that is defined NOT NULL,
- The result of a comparison between two NULL values is not true – you have to use the IS NOT NULL and IS NULL.
- If a column contains NULL values it will be ignored when using aggregations: MAX, AVG and SUM.
- When doing grouping using GROUP BY then there are special rows for the results.
- 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.
Tobias Trapp is SAP Mentor and developer for AOK Systems GmbH
