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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
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;