Collection Constructors:
|--+-SET------+--{--+-------------------------+--}--------------|
+-MULTISET-+ | .-,-------------------. |
'-LIST-----' | V (1) | |
'---| Expression |------+-'
Use a collection constructor to specify values for a collection column.
You can use collection constructors in the WHERE clause of the SELECT statement and the VALUES clause of the INSERT statement. You can also pass collection constructors to UDRs.
This table differentiates the types of collections that you can construct.
The element type of the collection can be any built-in or extended data type. You can use any kind of expression with a collection constructor, including literals, functions, and variables.
When you use a collection constructor with a list of expressions, the database server evaluates each expression to its equivalent literal form and uses the literal values to construct the collection.
You specify an empty collection with a set of empty braces ( { } ).
Elements of a collection cannot be NULL. If a collection element evaluates to a NULL value, the database server returns an error.
The element type of each expression must all be exactly the same data type. To accomplish this, cast the entire collection constructor expression to a collection type, or cast individual element expressions to the same type. If the database server cannot determine that the collection type and the element types are homogeneous, then the collection constructor returns an error. In the case of host variables, this determination is made at bind time when the client declares the element type of the host variable.
An exception to this restriction can occur when some elements of a collection are VARCHAR data types but others are longer than 255 bytes. Here the collection constructor can assign a CHAR(n) type to all elements, for n the length in bytes of the longest element. (But see Collection Data Types for an example based on this exception, where the user avoids fixed-length CHAR elements by an explicit cast to the LVARCHAR data type.)
The following example shows that you can construct a collection with various expressions, if the resulting values are of the same data type:
CREATE FUNCTION f (a int) RETURNS int;
RETURN a+1;
END FUNCTION;
CREATE TABLE tab1 (x SET(INT NOT NULL));
INSERT INTO tab1 VALUES
(
SET{10,
1+2+3,
f(10)-f(2),
SQRT(100) +POW(2,3),
(SELECT tabid FROM systables WHERE tabname = 'sysusers'),
'T'::BOOLEAN::INT}
)
SELECT * FROM tab1 WHERE
x=SET{10,
1+2+3,
f(10)-f(2),
SQRT(100) +POW(2,3),
(SELECT tabid FROM systables WHERE tabname = 'sysusers'),
'T'::BOOLEAN::INT}
}
This assumes that a cast from BOOLEAN to INT exists. (For a more restrictive syntax to specify collection values , see Literal Collection.)
The NULL keyword is valid in most contexts where you can specify a value. What it specifies, however, is the absence of any value (or an unknown or missing value).
Within SQL, the keyword NULL is the only syntactic mechanism for accessing a NULL value. NULL is not equivalent to zero, nor to any specific value. In ascending ORDER BY operations, NULL values precede any non-NULL value; in descending sorts, NULL values follow any non-NULL value. In GROUP BY operations, all NULL values are grouped together. (Such groups may in fact be logically heterogeneous, if they include missing or unknown values.)
The keyword NULL is a global symbol in the syntactic context of expressions, meaning that its scope of reference is global.
Every data type, whether built-in or user-defined, can represent a NULL value. IBM Informix Dynamic Server supports cast expressions in the projection list. This means that users can write expressions of the form NULL::datatype, in which datatype is any data type known to the database server.
IBM Informix Dynamic Server prohibits the redefinition of NULL, because allowing such definition would restrict the global scope of the NULL keyword. For this reason, any mechanism that restricts the global scope or redefines the scope of the keyword NULL will syntactically disable any cast expression involving a NULL value. You must ensure that the occurrence of the keyword NULL receives its global scope in all expression contexts.
For example, consider the following SQL code:
CREATE TABLE newtable ( null int ); SELECT null, null::int FROM newtable;
The CREATE TABLE statement is valid, because the column identifiers have a scope of reference that is restricted to the table definition; they can be accessed only within the scope of a table.
The SELECT statement in the example, however, poses some syntactic ambiguities. Does the identifier null appearing in the projection list refer to the global keyword NULL, or does it refer to the column identifier null that was declared in the CREATE TABLE statement?
A SELECT statement of the following form is valid because the NULL column of newtable is qualified with the table name:
SELECT newtable.null, null::int FROM newtable;
More involved syntactic ambiguities arise in the context of an SPL routine that has a variable named null. An example follows:
CREATE FUNCTION nulltest() RETURNING INT; DEFINE a INT; DEFINE null INT; DEFINE b INT; LET a = 5; LET null = 7; LET b = null; RETURN b; END FUNCTION; EXECUTE FUNCTION nulltest();
When the preceding function executes in DB-Access, in the expressions of the LET statement, the identifier null is treated as the keyword NULL. The function returns a NULL value instead of 7.
Using null as a variable of an SPL routine would restrict the use of a NULL value in the body of the SPL routine. Therefore, the preceding SPL code is not valid, and causes IBM Informix Dynamic Server to return the following error:
-947 Declaration of an SPL variable named 'null' conflicts with SQL NULL value.
In ESQL/C, you should use an indicator variable if there is the possibility that a SELECT statement will return a NULL value.
A function expression can return one or more values from built-in SQL functions or from user-defined functions, as the following diagram shows.
The following examples show function expressions:
EXTEND (call_dtime, YEAR TO SECOND)
MDY (12, 7, 1900 + cur_yr)
DATE (365/2)
LENGTH ('abc') + LENGTH (pvar)
HEX (customer_num)
HEX (LENGTH(123))
TAN (radians)
ABS (-32)
EXP (3)
MOD (10,3)
Enterprise Edition Home |
Express Edition Home |
[ Top of Page | Previous Page | Next Page | Contents |
Index ]