Example: Effect of Explicit NULLs on Data Type of a UNION - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Set operator results evaluate to the data type of the columns defined in the first SELECT statement in the operation. When a column in the first SELECT is defined as an explicit NULL, the data type of the result is not intuitive.

Consider the following two examples, which you might intuitively think would evaluate to the same result but do not.

In the first, an explicit NULL is selected as a column value.

   SELECT 'p', NULL
   FROM TableVM
   UNION
   SELECT 'q', 145.87
   FROM TableVM;

BTEQ returns the result as follows.

   'p'         Null
   ---  -----------
   p              ?
   q            145

The expected value for the second row of the Null column probably differs from what you might expect—a decimal value of 145.87.

What if the order of the two SELECTs in the union is reversed?

   SELECT 'q', 145.87
   FROM TableVM
   UNION
   SELECT 'p', NULL
   FROM TableVM;

BTEQ returns the result as follows.

   'q'       145.87
   ---  -----------
   p              ?
   q         145.87

The value for q is now reported as its true data type—DECIMAL—and without truncation. Why the difference?

In the first union example, the explicit NULL is specified for the second column in the first SELECT statement. The second column in the second SELECT statement, though specified as a DECIMAL number, evaluates to an integer because in this context, NULL, though having no value, does have the data type INTEGER, and that type is retained for the result of the union.

The second union example carries the data type for the value 145.87—DECIMAL—through to the result.

You can confirm the unconverted data type for NULL and 145.87 by performing the following SELECT statement.

   SELECT TYPE(NULL), TYPE(145.87)

BTEQ returns the result as follows.

   Type(Null)         Type(145.87)
   -----------------  ----------------------
   INTEGER            DECIMAL(5,2)