Restrictions - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

You cannot make relational comparisons of ARRAY values. You cannot use any of the relational comparison operators on ARRAY data. You can only make relational comparisons on individual elements of an ARRAY.

Examples: Querying a Table using ARRAY_COMPARE

In this example, the inventory table contains two ARRAY columns, sale_price_june and sale_price_july, which contain the following element values:

sale_price_june[1:10] = 50, 100, 200, 90, 250, 550, 200, 200, 50, 75
sale_price_july[1:10] = 50, 100, 200, 90, 250, 550, 200, 200, 50, 75

The output of the following query is 1 since all of the element values in the sale_price_june array are equal to the element values in the sale_price_july array.

SELECT ARRAY_COMPARE(sale_price_june, sale_price_july)
FROM inventory;

The following is the same query using method-style syntax.

SELECT sale_price_june.ARRAY_COMPARE(sale_price_july)
FROM inventory;

Consider the same arrays but with different element values as follows:

sale_price_june[1:10] = 50, 100, 200, 90, 250, 550, 200, 200, 50, 75
sale_price_july[1:10] = 50, 100, 200, 90, 300, 550, 200, 200, 50, 75

The output of the following query is 0 since the element value in position 5 differs in the two arrays.

SELECT ARRAY_COMPARE(sale_price_june, sale_price_july)
FROM inventory;

The following query compares the values of elements in positions 6 through 10. The output of this query is 1 since the element values in the given positions of both arrays are equal.

SELECT ARRAY_COMPARE(sale_price_june, sale_price_july, 6, 10)
FROM inventory;