init_spec Clause

The init_spec clause is used to specify when a field should be set to NULL or when it should be set to a default value. The syntax for the init_spec clause is as follows:

Description of the illustration et_init_spec.eps follows
Description of the illustration et_init_spec.eps

Only one NULLIF clause and only one DEFAULTIF clause can be specified for any field. These clauses behave as follows:

  • If NULLIF condition_spec is specified and it evaluates to TRUE, then the field is set to NULL.

  • If DEFAULTIF condition_spec is specified and it evaluates to TRUE, then the value of the field is set to a default value. The default value depends on the data type of the field, as follows:

    • For a character data type, the default value is an empty string.

    • For a numeric data type, the default value is a 0.

    • For a date data type, the default value is NULL.

  • If a NULLIF clause and a DEFAULTIF clause are both specified for a field, then the NULLIF clause is evaluated first and the DEFAULTIF clause is evaluated only if the NULLIF clause evaluates to FALSE.