Example: Effect of Explicit NULLs on Data Type of a UNION - 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™

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)