DEFAULTIF (fieldSelection)

Use DEFAULTIF(fieldSelection) to conditionally specifies whether a table column should receive a input data value (if provided) or a Db2 default value. You can define DEFAULTIF clauses for any column, regardless of whether the column has the WITH DEFAULT attribute.

You cannot specify DEFAULTIF for the following columns:

ROWID
LOB, CLOB, DBCLOB, XML
ROW BEGIN, ROW END, and ROW CHANGE

You can specify DEFAUILTIF for IDENTITY columns if you use OVERRIDE(IDENTITY). The DEFAULTIF(fieldSelection) variable that you specify uses the same syntax as the field selection specified on a WHEN clause. For more information, see the field selection syntax diagram.

Defaultifvalue selection

In a DEFAULTIF statement, you can specify a default VALUE value that overrides any Db2 default values specified for the column when the table was created.

Value specification syntax

VALUE values

Value	Description
constant	A constant
special register	A special register
SQL-constant expression	An SQL expression involving constants, or special registers, or both
NULL	Equivalent to a NULLIF clause

For more information about SQL constant and special register, see BMC-AMI-Utilities-SQL-language.

Examples

Example 1

This example involves using constants in the VALUE specification.
Assume that COL1 is defined as VARCHAR(10) DEFAULT ‘ORANGE’

COL1 POSITION(1:2) CHAR
DEFAULTIF COL1=’TX’ VALUE(‘RED’)
DEFAULTIF COL1=’CA’ VALUE(‘BLUE’)
DEFAULTIF COL1<>’CA’

If the first two bytes of the input field are TX, the result of the VARCHAR will be set to RED. If the first two bytes of the input field are CA, the result of the VARCHAR will be set to BLUE. Otherwise, the result of the VARCHAR will be set to ORANGE.

Example 2

This example shows how to use an SQL expression involving constants and special registers.
Assume that COL2 is defined as CHAR(1) DEFAULT NULL.

COL2 POSITION(1) CHAR(1)
DEFAULTIF (1:1)=’?’ VALUE(CASE WHEN YEAR(CURRENT DATE)//4<>0 THEN ‘N’
   WHEN YEAR(CURRENT DATE)//100<>0 THEN ‘L’
   WHEN YEAR(CURRENT DATE)//400=0 THEN ‘L’
   ELSE ‘N’
  END)
DEFAULTIF (1:1)<>’?’

If the first byte of the input field is ‘?’, the result of COL2 will be set to

‘L’ if the current year is a leap year
‘N’ if the current year is not a leap year

However, if the first byte of the input field is not ‘?’, the result of COL2 will be set to the Db2 default value of NULL.

The following table describes default values that BMC AMI Load generates when a DEFAULTIF condition is true and the column has no default value specified in the Db2 catalog:

Column type	Default value
Character or graphic	Blank
Numeric	0
Date	Current date
Time	Current time
Timestamp	Current time stamp

The current date, time, and timestamp values are recomputed for each inserted row and therefore results might not be the same across the entire table.

DEFAULTIF (fieldSelection)

Defaultifvalue selection

Examples

On this page