Example: DEFAULT Function in a WHERE Clause Search Condition - Teradata Vantage

The following set of examples shows the DEFAULT function in a WHERE clause search condition.

The examples assume these table definitions:

     CREATE TABLE table14 (
       col1 INTEGER,
       col2 INTEGER DEFAULT 10,
       col3 INTEGER DEFAULT 20,
       col4 CHARACTER(60) );

     CREATE TABLE table15 (
       col1 INTEGER ,
       col2 INTEGER NOT NULL,
       col3 INTEGER NOT NULL DEFAULT NULL,
       col4 INTEGER CHECK (col4 > 10) DEFAULT 9 );

     CREATE TABLE table16 (
       col1 INTEGER,
       col2 INTEGER DEFAULT 10,
       col3 INTEGER DEFAULT 20,
       col4 CHARACTER(60)  );

In this statement, the DEFAULT function evaluates to the default value of col2, which is 10.

     SELECT col2, col3
     FROM table16
     WHERE col1 < DEFAULT(col2);

Following is an equivalent statement that uses an explicitly defined search condition in the WHERE clause.

     SELECT col2, col3
     FROM table16
     WHERE col1 < 10;

You can specify a DEFAULT function with a column name argument within a predicate.

In this statement, the DEFAULT function evaluates to the default value of col3, which is 20.

     SELECT col2, col3
     FROM table16
     WHERE col1 + 9 > DEFAULT(col3)+ 8;

Following is an equivalent statement that uses an explicitly defined search condition in the WHERE clause.

     SELECT col2, col3
     FROM table16
     WHERE col1 + 9 > 20 + 8;

In this example, the DEFAULT function evaluates to the default value of col3, compares the returned value with the result of a subquery, and returns the result.

     SELECT col2, col3
     FROM table16
     WHERE DEFAULT(col3) < ANY (SELECT col2
                                FROM table14);

You can specify a DEFAULT function without a column name argument in a comparison predicate when there is only one column specification and one DEFAULT function as the terms on the each side of the comparison operator within the expression.

In this example, there is only one column reference, and the DEFAULT function is compared directly with it; therefore, the DEFAULT function evaluates to the default value of col2.

     SELECT col2, col3
     FROM table16
     WHERE col2 > DEFAULT;

The following two statements are semantically equivalent.

     SELECT col2, col3
     FROM table16
     WHERE col2 > DEFAULT
     AND   DEFAULT > col3;

     SELECT col2, col3
     FROM table16
     WHERE col2 > DEFAULT(col2)
     AND   DEFAULT(col3) > col3;

Following the existing comparison rules, a condition with a DEFAULT function used with comparison operators other than IS NULL or IS NOT NULL is unknown if the DEFAULT function evaluates to null.

Assume this table definition.

     CREATE TABLE table17 (
       col1 INTEGER,
       col2 INTEGER NOT NULL DEFAULT 10,
       col3 INTEGER DEFAULT NULL );

In this example, the DEFAULT function evaluates to NULL, the predicate is UNKNOWN, and the WHERE condition is FALSE; therefore, the query returns no rows.

     SELECT col1
     FROM table17
     WHERE col1 = DEFAULT;

In this example, the DEFAULT function evaluates to NULL. The first condition, DEFAULT(col3) > 5, is UNKNOWN, but the second condition is TRUE; therefore, the WHERE condition is TRUE and the statement returns all of the rows from table17.

     SELECT col1, col2
     FROM table17
     WHERE DEFAULT(col3) > 5
     OR    DEFAULT(col3) IS NULL;

In this example, the DEFAULT function evaluates to NULL, the predicate is UNKNOWN, and the WHERE condition is FALSE. Therefore, the statement does not return any rows.

     SELECT col1
     FROM table17
     WHERE col2 < DEFAULT(col3) + 3;

In this example, the DEFAULT function evaluates to 10 and the first condition is TRUE. The second condition, DEFAULT(col2) IS NULL, is FALSE; therefore, the WHERE condition is TRUE and the statement returns all of the rows from table17.

     SELECT col1, col2
     FROM table17
     WHERE DEFAULT(col2) > 5
     OR    DEFAULT(col2) IS NULL;