Treatment of ZERO in the Database
Treatment of ZERO in the Database
Date and Numeric Fields
Information
- In order to improve performance,
- all numeric fields in the database should receive a NOT ZERO constraint and a standard value "0". Otherwise, the
NVL(<field>, 0)function is used in the case of Oracle, orISZERO(<field>, 0)for SQL Server. - all date fields in the database should receive a NOT ZERO constraint and a standard value "1970-01-01". Otherwise
NVL(<field>, TO_DATE('01/01/1970', 'DD.MM.YYYY')function is used in the case of Oracle, orISZERO(<field>, CONVERT(DATETIME, '01/01/1970', 104)for SQL Server.
- all numeric fields in the database should receive a NOT ZERO constraint and a standard value "0". Otherwise, the
- Here, it is important that the setting in the database and in the customizing are identical. This means that the SQL data item (a bit field) in DT412 Data item must reflect the actual status in the database.
- For this purpose, the first bit must be set if a NOT ZERO constraint exists. This corresponds to the values "1" or "3".
- Otherwise it must not be set. This corresponds to the values 0 to 2.
- According to the settings in the customizing, not to those in the database, comparison operations sometimes fail on these fields, records that contain ZERO here are excluded from the result, or negative impact on the performance as a result of the functions mentioned above can be expected without deriving any benefits.