1. SQL Reference Guide
  2. SQL Query Management
  3. Sequence Types

Sequence Types

A sequence type specifies the type of items that may appear in a sequence, as well as an indication of the cardinality of the sequence.

Syntax

sequence_type ::= type_definition [quantifier]

quantifier := "*" | "+" | "?"

Semantics

quantifier
The quantifier is one of the following:
  • * indicates a sequence of zero or more items.
  • + indicates a sequence of one or more items.
  • ? indicates a sequence of zero or one items.
  • The absence of a quantifier indicates a sequence of exactly one item.

subtype relationship

A subtype relationship exists among sequence types as well. It is defined as follows:
  • The empty sequence is a subtype of all sequence types whose quantifier is * or ?
  • A sequence type SUB is a subtype of another sequence type SUP (supertype) if SUB's item type is a subtype of SUP's item type, and SUB's quantifier is a subquantifier of SUP's quantifier, where the subquantifier relationship is defined by the following matrix.
The following matrix illustrates the subquantifier relationship between the quantifiers. The column heading indicate the supertype(SUP) of the quantifier. The row heading indicate the subtype (SUB) of the quantifier.
Sup Q1 | Sub Q2 one ? + *
one true false false false
? true true false false
+ true false true false
* true true true true
For example, as per the above table, ? is a superquantifier of one and ?, but is not a superquantifier of + and *. Similarly, * is a superquantifier of all other quantifiers.

Note:

In the following sections, when we say that an expression must have (sequence) type T, what we mean is that when the expression is evaluated, the result sequence must have type T or any subtype of T. Similarly, the usual subtype-substitution rules apply to input sequences: if an expression expects as input a sequence of type T, any subtype of T may actually be used as input.

If you want to follow along with the examples, create the airline application table and insert data as described in Tables used in the Examples.

Example 6-3 Fetch the details of passengers who fly from SFO/through SFO to any other location in an airline application

SELECT bag.fullname, 
bag.bagInfo[].tagNum, 
bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"

Explanation: In an airline application, you can get the details of all the passengers who have traveled from a specific station. The flightLegs array in the bagInfo field contains the source and destination stations for each travel leg. The bagInfo.flightLegs[].fltRouteSrc is a string sequence that holds the source stations from where the passengers board the flight.

In this query, you compare the source stations with the input string, SFO, to fetch the list of passengers traveling from the SFO station. Since the comparison operators cannot operate on sequences of more than one item, you use the sequence comparison operator =any to compare the bagInfo.flightLegs[].fltRouteSrc sequence with the required station. For more details on sequence comparison, see Sequence Comparison Operators.

Here, the source station can either be the flights originating from SFO, or transiting through SFO station to any other destination. The bag is a table alias for the Baggageinfo table and essentially functions as a variable. The variables are bound to the context row as a whole and can be referenced inside the hierarchically structured data.

Output:
{"fullname":"Henry Jenkins","tagNum":"17657806216554","fltRouteSrc":["SFO","ORD"]}
{"fullname":"Michelle Payne","tagNum":"17657806247861","fltRouteSrc":["SFO","IST","ATH"]}
{"fullname":"Gerard Greene","tagNum":"1765780626568","fltRouteSrc":["SFO","IST","ATH"]}
{"fullname":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],"fltRouteSrc":["SFO","IST","ATH","SFO","IST","ATH"]}
{"fullname":"Lucinda Beckman","tagNum":"17657806240001","fltRouteSrc":["SFO","IST","ATH"]}