Example: Inserting the Default Value Under Certain Conditions

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

Consider the following Employee table definition:

   CREATE TABLE Employee
      (Emp_ID      INTEGER
      ,Last_Name   VARCHAR(30)
      ,First_Name  VARCHAR(30)
      ,Dept_No     INTEGER DEFAULT 99
   );

The following statement uses DEFAULT to insert the default value of the Dept_No column when the supplied value is negative.

   USING (id INTEGER, n1 VARCHAR(30), n2 VARCHAR(30), dept INTEGER)
   INSERT INTO Employee VALUES
      (:id
      ,:n1
      ,:n2
      ,CASE WHEN (:dept < 0) THEN DEFAULT(Dept_No) ELSE :dept END
   );