About the Data Vault SQL Reference
Introduction to SQL Reference for Informatica Data Vault
Date and Time Arithmetic
WHERE Clauses
UNION Operator
Parameterized Query
Functions
- Functions Overview
- ABS
- AVG
- CEILING
- CHAR
- COALESCE
- CONCAT
- COUNT
- DATE
- DATE (Date and Time)
- DAY
- DEC
- DIGITS
- EXP
- EXTRACT
- FLOOR
- HOUR
- IFNULL
- INT
- LASTDAY
- LEFT
- LEN
- LN
- LOG10
- LOWER
- LTRIM
- MAX
- MICROSECOND
- MIN
- MINUTE
- MOD
- MONTH
- NANOSECOND
- NULLIF
- PI
- POSITION
- POSSTR
- POWER
- REPLACE
- RIGHT
- ROUND
- RTRIM
- SECOND
- SIGN
- SQRT
- SUBSTRING
- SUM
- TIME
- TODAY
- TRIM
- UPPER
- YEAR

Data Vault SQL Reference

6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.4
- 6.4.3 HotFix 1
- 6.4.3

Back Next

Guidelines for Using the UNION Operator

Unless otherwise noted in the guidelines, the Data Vault Service follows the standard SQL rules for the UNION operator.

Use the following guidelines when you include UNION or UNION ALL in a query:

You can use parentheses to specify the order of evaluation.

The following example is a valid UNION query for use with the Data Vault Service:

SELECT c1, c2, c3 FROM Table1
UNION
(SELECT c1, c2, c3 FROM Table2
UNION
SELECT c1, c2, c3 FROM Table3);

In this example, the Data Vault Service evaluates the union between Table2 and Table3. Then the Data Vault Service evaluates the union between Table1 and the result set of the union between Table2 and Table3.

If you use the Data Vault Service ssasql command line program to run queries, you cannot use parentheses to enclose the first SELECT statement in a UNION query.

The following example is not a valid use of parentheses in a UNION query that you run from the Data Vault Service ssasql command line program:

(SELECT c1, c2, c3 FROM Table1)
UNION
SELECT c1, c2, c3 FROM Table2;

The number of columns selected from a table must be the same for each SELECT statement. If you select all columns from a table with SELECT *, the number of columns in the table must be the same as the number of columns selected for other tables.

The data types of the selected columns must be compatible. For example, integer and decimal data types are compatible. In the following example, LastName from Customers is compatible with VendorName from Suppliers, City is compatible with City, MembershipStart is compatible with LastUpdated.

SELECT LastName, City, MembershipStart FROM Customers
UNION
SELECT VendorName, City, LastUpdated FROM Suppliers
ORDER BY City;

The columns in the final result set take the names of the columns in the first SELECT statement. For example, the columns in the final result set for the following query will be named LastName and City.

SELECT LastName, City FROM Customers
UNION
SELECT VendorName, City FROM Suppliers
ORDER BY City;

The columns in the final result set take the largest column size in all the SELECT statements. For example, if the SELECT statements include a column with small integer, integer, and decimal data types, the datatype for the column in final result set will be decimal.

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

Guidelines for Using the UNION Operator

Guidelines for Using the UNION Operator