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(<Feld>, 0) function is used in the case of Oracle, or ISZERO(<Feld>, 0) for SQL Server.
    • all numeric fields in the database should receive a NOT ZERO constraint and a standard value "1970-01-01". Otherwise NVL(<Feld>, TO_DATE('01/01/1970', 'DD.MM.YYYY') function is used in the case of Oracle, or ISZERO(<Feld>, CONVERT(DATETIME, '01/01/1970', 104) for SQL Server.
  • 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.