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)