Informix Guide to SQL: Syntax Segments

An expression is one or more pieces of data that is contained in a table or derived from data in the table. Typically you use expressions to express values in data manipulation statements. Use the Expression segment whenever you see a reference to an expression in a syntax diagram.

For an alphabetical listing of the built-in functions in this segment, see List of Expressions.

Usage

This segment describes SQL expressions. The following table shows the different types of SQL expressions as shown in the diagram for Syntax and states the purpose of each type.

You can also use host variables or SPL variables as expressions. For a complete list of SQL expressions, see List of Expressions.

Each category of SQL expression includes many individual expressions. The following table lists all the SQL expressions in alphabetical order and states the purpose of each expression. The columns in this table have the following meanings:

• The Name column gives the name of each expression.
• The Purpose column shows the purpose of each expression.
• The Syntax column refers to the page that describes the syntax of the expression.
• The Usage column refers to the page that describes the usage of the expression.

Each expression listed in the following table is supported on all database servers unless otherwise noted. When an expression is not supported on all database servers, the Name column notes in parentheses the database server or servers that do support the expression.

Name	Purpose	Syntax	Usage
ABS function	Returns the absolute value of a given expression	p. 4-123	p. 4-125
ACOS function	Returns the arc cosine of a numeric expression	p. 4-159	p. 4-161
Addition operator (+)	Adds two expressions to make a complex expression	p. 4-74	p. 4-85
ASIN function	Returns the arc sine of a numeric expression	p. 4-159	p. 4-161
ATAN function	Returns the arc tangent of a numeric expression	p. 4-159	p. 4-161
ATAN2 function	Computes the angular component of the polar coordinates (r, ) associated with (x, y)	p. 4-159	p. 4-162
AVG function	Returns the average of all values in the specified column or expression	p. 4-185	p. 4-194
CARDINALITY function (IDS)	Returns the number of elements in a collection column (SET, MULTISET, or LIST)	p. 4-129	p. 4-129
CASE expression	Returns one of several possible results, depending on which of several conditional tests evaluates to true	p. 4-100	p. 4-100
CAST expression (IDS)	Provides the ability to cast an expression to another data type	p. 4-88	p. 4-88
Cast operator	See Double-colon cast operator.	p. 4-88	p. 4-88
CHARACTER_LENGTH function	See CHAR_LENGTH function.	p. 4-143	p. 4-145
CHAR_LENGTH function	Returns the number of logical characters (not bytes) in a character column	p. 4-143	p. 4-145
Column expression	Complete or partial column value from a table	p. 4-91	p. 4-91
Concatenation operator	Concatenates the output of two expressions	p. 4-74	p. 4-87
Constant expression	Expression that evaluates to a constant value	p. 4-108	p. 4-108
COS function	Returns the cosine of a radian expression	p. 4-159	p. 4-160
COUNT (as a set of functions)	Provides a set of functions for counting column values and expressions You invoke each function by specifying the appropriate argument after the COUNT keyword. Each form of the COUNT function is listed below.	p. 4-185	p. 4-189
COUNT (ALL column) function	See COUNT (column) function.	p. 4-185	p. 4-190
COUNT (column) function	Returns the number of non-null values in a specified column	p. 4-185	p. 4-190
COUNT DISTINCT function	Returns the number of unique non-null values in a specified column	p. 4-185	p. 4-190
COUNT UNIQUE function	See COUNT DISTINCT function.	p. 4-185	p. 4-190
COUNT (*) function	Returns the number of rows that satisfy a query. If you do not specify a WHERE clause, this function returns the total number of rows in the table.	p. 4-185	p. 4-189
CURRENT function	Shows the current instant by returning a DATETIME value consisting of the date and the time of day	p. 4-108	p. 4-112
DATE function	Returns a DATE value that corresponds to the non-date expression with which you call it	p. 4-152	p. 4-154
DAY function	Returns an integer that represents the day of the month	p. 4-152	p. 4-155
DBINFO (as a set of functions)	Provides a set of functions for retrieving different types of database information You invoke each function by specifying the appropriate DBINFO option. Each DBINFO option is listed below.	p. 4-130	p. 4-131
DBINFO ('coserverid' string followed by a column name qualified by a table name and the 'currentrow' string) (EDS)	Returns the coserver ID of the coserver where each row of a specified table is located	p. 4-130	p. 4-139
DBINFO ('coserverid' string with no other arguments) (EDS)	Returns the coserver ID of the coserver to which the user who entered the query is connected	p. 4-130	p. 4-139
DBINFO ('dbhostname' option)	Returns the hostname of the database server to which a client application is connected	p. 4-130	p. 4-136
DBINFO ('dbspace' string followed by a column name qualified by a table name and the 'currentrow' string) (EDS)	Returns the name of the dbspace where each row of a specified table is located	p. 4-130	p. 4-140
DBINFO ('dbspace' string followed by a tblspace number)	Returns the name of a dbspace corresponding to a tblspace number	p. 4-130	p. 4-133
DBINFO ('serial8' option) (IDS)	Returns the last SERIAL8 value inserted in a table	p. 4-130	p. 4-138
DBINFO ('sessionid' option)	Returns the session ID of the current session	p. 4-130	p. 4-135
DBINFO ('sqlca.sqlerrd1' option)	Returns the last serial value inserted in a table	p. 4-130	p. 4-133
DBINFO ('sqlca.sqlerrd2' option)	Returns the number of rows processed by selects, inserts, deletes, updates, EXECUTE PROCEDURE statements, and EXECUTE FUNCTION statements	p. 4-130	p. 4-134
DBINFO ('version' option)	Returns the exact version of the database server to which a client application is connected	p. 4-130	p. 4-136
DBSERVERNAME function	Returns the name of the database server	p. 4-108	p. 4-111
DECODE function	Evaluates one or more expression pairs and compares the when expression in each pair against a specified value expression When the DECODE function finds a match between the when expression in an expression pair and the specified value expression, it returns the value of the then expression in that expression pair.	p. 4-105	p. 4-105
Division operator (/)	Divides one expression by another to make a complex expression	p. 4-74	p. 4-85
Double-colon cast operator (::) (IDS)	Provides the ability to cast an expression to another data type	p. 4-88	p. 4-88
EXP function	Returns the exponent of a numeric expression	p. 4-141	p. 4-141
EXTEND function	Adjusts the precision of a DATETIME or DATE value	p. 4-152	p. 4-156
FILETOBLOB function (IDS)	Creates a BLOB value for data that is stored in a specified operating-system file	p. 4-146	p. 4-147
FILETOCLOB function (IDS)	Creates a CLOB value for data that is stored in a specified operating-system file	p. 4-146	p. 4-147
HEX function	Returns the hexadecimal encoding of an integer expression	p. 4-142	p. 4-142
Host variable	See Variable.	p. 4-74	p. 4-74
IFX_ALLOW_NEWLINE function	Sets a newline mode that allows newline characters in a quoted strings or disallows newline characters in quoted strings within a given session	p. 4-177	p. 4-177
IFX_REPLACE_MODULE function (IDS)	Replaces a loaded shared library with a new version that has a different name or location	p. 4-145	p. 4-145
INITCAP function	Converts a source expression so that every word in the source expression begins with an initial capital letter and all remaining letters in each word are lowercase	p. 4-174	p. 4-177
LENGTH function	Returns the number of bytes in a character column, not including any trailing spaces	p. 4-143	p. 4-144
LIST collection constructor (IDS)	Enables you to specify values for collection columns The LIST constructor indicates a collection of elements with the following qualities: The collection can contain duplicate values. Elements have ordered positions.	p. 4-118	p. 4-118
Literal BOOLEAN (as an expression)	Provides a literal representation of a BOOLEAN value	p. 4-108	p. 4-108
Literal collection (as an expression) (IDS)	Provides a constant value in data manipulation statements	p. 4-108	p. 4-115
Literal DATETIME (as an expression)	Provides a constant value in data manipulation statements	p. 4-108	p. 4-114
Literal INTERVAL (as an expression)	Provides a constant value in data manipulation statements	p. 4-108	p. 4-114
Literal number (as an expression)	Provides a constant value in data manipulation statements	p. 4-108	p. 4-110
Literal opaque type (as an expression) (IDS)	Provides a literal representation of an opaque data type	p. 4-108	p. 4-108
Literal row (as an expression) (IDS)	Provides a constant value in data manipulation statements	p. 4-108	p. 4-115
LOCOPY function (IDS)	Creates a copy of a smart large object	p. 4-146	p. 4-150
LOGN function	Returns the natural log of a numeric expression	p. 4-141	p. 4-142
LOG10 function	Returns the log of a value to the base 10	p. 4-141	p. 4-142
LOTOFILE function (IDS)	Copies a smart large object to an operating-system file	p. 4-146	p. 4-149
LOWER function	Converts a source expression to lowercase characters	p. 4-174	p. 4-176
LPAD function	Returns a copy of a source string that is left-padded by a specified number of pad characters	p. 4-172	p. 4-172
MAX function	Returns the largest value in the specified column or expression	p. 4-185	p. 4-194
MDY function	Returns a DATE value with three expressions that evaluate to integers representing the month, day, and year	p. 4-152	p. 4-157
MIN function	Returns the lowest value in the specified column or expression	p. 4-185	p. 4-194
MOD function	Returns the modulus or remainder value for two numeric expressions	p. 4-123	p. 4-126
MONTH function	Returns an integer that corresponds to the month portion of its DATE or DATETIME argument	p. 4-152	p. 4-155
Multiplication operator (*)	Multiplies two expressions to make a complex expression	p. 4-74	p. 4-85
MULTISET collection constructor (IDS)	Enables you to specify values for collection columns The MULTISET constructor indicates a collection of elements with the following qualities: The collection can contain duplicate values. Elements have no specific order associated with them.	p. 4-118	p. 4-118
NVL function	Evaluates an expression and returns the value of the expression if the value of the expression is not null If the value of the expression is null, the NVL function returns a specified result.	p. 4-104	p. 4-104
OCTET_LENGTH function	Returns the number of bytes in a character column, including any trailing spaces	p. 4-143	p. 4-144
POW function	Raises a base value to a specified power	p. 4-123	p. 4-126
Procedure-call expression	See User-defined function.	p. 4-179	p. 4-179
Program variable	See Variable.	p. 4-74	p. 4-74
Quoted string (as an expression)	Provides a constant value in data manipulation statements	p. 4-108	p. 4-109
RANGE function	Computes the range for a sample of a population	p. 4-185	p. 4-195
REPLACE function	Replaces specified characters in a source string with different characters	p. 4-170	p. 4-170
ROOT function	Returns the root value of a numeric expression	p. 4-123	p. 4-126
ROUND function	Returns the rounded value of an expression	p. 4-123	p. 4-127
ROW constructor (IDS)	Enables you to specify values for columns that are named row types	p. 4-116	p. 4-116
RPAD function	Returns a copy of a source string that is right-padded by a specified number of pad characters	p. 4-173	p. 4-173
SET collection constructor (IDS)	Enables you to specify values for collection columns The SET constructor indicates a collection of elements with the following qualities: The collection must contain unique values. Elements have no specific order associated with them.	p. 4-118	p. 4-118
SIN function	Returns the sine of a radian expression	p. 4-159	p. 4-161
SITENAME function	See DBSERVERNAME function.	p. 4-108	p. 4-111
SPL routine expression	See User-defined functions.	p. 4-179	p. 4-179
SPL variable	SPL variable that stores an expression	p. 4-74	p. 4-74
SQRT function	Returns the square root of a numeric expression	p. 4-123	p. 4-128
Statement-Local-Variable expression	Specifies how you can use a defined statement-local variable (SLV) elsewhere in an SQL statement	p. 4-183	p. 4-183
STDEV function	Computes the standard deviation for a sample of a population	p. 4-185	p. 4-196
SUBSTR function	Returns a subset of a source string	p. 4-168	p. 4-168
SUBSTRING function	Returns a subset of a source string	p. 4-166	p. 4-166
Subtraction operator (-)	Subtracts one expression from another to make a complex expression	p. 4-74	p. 4-85
SUM function	Returns the sum of all values in the specified column or expression	p. 4-185	p. 4-195
TAN function	Returns the tangent of a radian expression	p. 4-159	p. 4-161
TO_CHAR function	Converts a DATE or DATETIME value to a character string	p. 4-152	p. 4-157
TO_DATE function	Converts a character string to a DATETIME value	p. 4-152	p. 4-158
TODAY function	Returns the system date	p. 4-108	p. 4-112
TRIM function	Removes leading or trailing (or both) pad characters from a string	p. 4-164	p. 4-164
TRUNC function	Returns the truncated value of a numeric expression	p. 4-123	p. 4-128
UNITS keyword	Enables you to display a simple interval or increase or decrease a specific interval or datetime value	p. 4-108	p. 4-114
UPPER function	Converts a source expression to uppercase characters	p. 4-174	p. 4-176
User-defined aggregate (IDS)	An aggregate that you write (as opposed to the built-in aggregates provided by the database server)	p. 4-199	p. 4-199
User-defined function	A function that you write (as opposed to the built-in functions provided by the database server)	p. 4-179	p. 4-179
USER function	Returns a string that contains the login name of the current user	p. 4-108	p. 4-110
Variable	host variable or program variable that stores an expression	p. 4-74	p. 4-74
VARIANCE function	Returns the variance for a sample of values as an unbiased estimate of the variance of a population	p. 4-185	p. 4-196
WEEKDAY function	Returns an integer that represents the day of the week	p. 4-152	p. 4-155
YEAR function	Returns a four-digit integer that represents the year	p. 4-152	p. 4-156
* sign	See Multiplication operator.	p. 4-74	p. 4-85
+ sign	See Addition operator.	p. 4-74	p. 4-85
- sign	See Subtraction operator.	p. 4-74	p. 4-85
/ sign	See Division operator.	p. 4-74	p. 4-85
:: symbol	See Double-colon cast operator.	p. 4-88	p. 4-88

The following sections describe the syntax and usage of each expression that appears in the preceding table.

You can combine expressions with arithmetic operators to make complex expressions. To combine expressions, connect them with the following binary arithmetic operators.

The following examples use binary arithmetic operators:

quantity * total_price 



price * 2 



COUNT(*) + 2 

If you combine a DATETIME value with one or more INTERVAL values, all the fields of the INTERVAL value must be present in the DATETIME value; no implicit EXTEND function is performed. In addition, you cannot use YEAR to MONTH intervals with DAY to SECOND intervals.

The binary arithmetic operators have associated operator functions, as the preceding table shows. Connecting two expressions with a binary operator is equivalent to invoking the associated operator function on the expressions. For example, the following two statements both select the product of the total_price column and 2. In the first statement, the * operator implicitly invokes the times() function.

SELECT (total_price * 2) FROM items 

	WHERE order_num = 1001

SELECT times(total_price, 2) FROM items

	WHERE order_num = 1001

You cannot combine expressions that use aggregate functions with column expressions.

The database server provides the operator functions associated with the relational operators for all built-in data types.You can define new versions of these binary arithmetic operator functions to handle your own user-defined data types. For more information, see Extending Informix Dynamic Server 2000.

Informix also provides the following unary arithmetic operators:.

The unary arithmetic operators have the associated operator functions that the preceding table shows. You can define new versions of these arithmetic operator functions to handle your own user-defined data types. For more information on how to write versions of operator functions, see Extending Informix Dynamic Server 2000.

If any value that participates in an arithmetic expression is null, the value of the entire expression is null, as shown in the following example:

SELECT order_num, ship_charge/ship_weight FROM orders

	WHERE order_num = 1023

If either ship_charge or ship_weight is null, the value returned for the expression ship_charge/ship_weight is also null. If the expression ship_charge/ship_weight is used in a condition, its truth value is unknown.

You can use the concatenation operator (||) to concatenate two expressions. For example, the following examples are some possible concatenated-expression combinations. The first example concatenates the zipcode column to the first three letters of the lname column. The second example concatenates the suffix .dbg to the contents of a host variable called file_variable. The third example concatenates the value returned by the TODAY function to the string Date.

lname[1,3] || zipcode 



:file_variable || '.dbg'



'Date:' || TODAY  

You cannot use the concatenation operator in an embedded-language-only statement. The ESQL/C-only statements appear in the following list:

You can use the concatenation operator in the SELECT, INSERT, EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement in the DECLARE statement.

You can use the concatenation operator in the SQL statement or statements in the PREPARE statement.

The concatenation operator (||) has an associated operator function called concat(). You can define a concat() function to handle your own string-based user-defined data types. For more information, see Extending Informix Dynamic Server 2000.

You can use the CAST AS keywords or the double-colon cast operator (::) to cast an expression to another data type. Both the operator and the keywords invoke a cast from the data type of the expression to the target data type. To invoke an explicit cast you must use either the cast operator or the CAST AS keywords. If you use the cast operator or CAST AS keywords, but no explicit or implicit cast was defined to perform the conversion between two data types, the statement returns an error.

You must observe the following rules and restrictions regarding the target data type parameter.

• The target data type must be either a built-in type, a user-defined type, or a named row type in the database.
• The target data type cannot be an unnamed row type or a collection data type.
• The target data type can be a BLOB data type under the following conditions:
• The source expression (the expression to be cast to another data type) is a BYTE data type.
• The source expression is a user-defined type and the user has defined a cast from the user-defined type to the BLOB type.
• The target data type can be a CLOB data type under the following conditions:
• The source expression is a TEXT data type.
• The source expression is a user-defined type and the user has defined a cast from the user-defined type to the CLOB type.
• You cannot cast a BLOB data type to a BYTE data type.
• You cannot cast a CLOB data type to a TEXT data type.
• An explicit or implicit cast must exist that can convert the data type of the source expression to the target data type.

The following examples show two different ways to convert the sum of x and y to a user-defined data type, user_type. The two methods produce identical results. Both require the existence of an explicit or implicit cast from the type returned by x + y to the user-defined type.

CAST (x + y) AS user_type

(x + y)::user_type

The following examples show two different ways of finding the integer equivalent of the expression expr. Both require the existence of an implicit or explicit cast from the data type of expr to the INTEGER data type.

CAST expr AS INTEGER

expr::INTEGER

In the following example, the user casts a BYTE column to the BLOB type and copies the BLOB data to an operating-system file:

SELECT LOTOFILE(mybytecol::blob, 'fname', 'client')

	FROM mytab 

	WHERE pkey = 12345

In the following example, the user casts a TEXT column to a CLOB value and then updates a CLOB column in the same table to have the CLOB value derived from the TEXT column:

UPDATE newtab SET myclobcol = mytextcol::clob

The possible syntax for column expressions is shown in the following diagram.

The following examples show column expressions:

company 



items.price 



cat_advert [1,15]

Use a table or alias name whenever it is necessary to distinguish between columns that have the same name but are in different tables. The SELECT statements that the following example shows use customer_num from the
customer and orders tables. The first example precedes the column names with table names. The second example precedes the column names with table aliases.

SELECT * FROM customer, orders 

	WHERE customer.customer_num = orders.customer_num



SELECT * FROM customer c, orders o

	WHERE c.customer_num = o.customer_num

Dot notation allows you to qualify an SQL identifier with another SQL identifier. You separate the identifiers with the period (.) symbol. For example, you can qualify a column name with any of the following SQL identifiers:

• Table name: table_name.column_name
• View name: view_name.column_name
• Synonym name: syn_name.column_name

The previous forms of dot notation are called column projections.

You can also use dot notation to directly access the fields of a row column, as follows:

row-column name.field name

This use of dot notation is called a field projection. For example, suppose you have a column called rect with the following definition:

CREATE TABLE rectangles

(

	area float,

	rect ROW(x int, y int, length float, width float)

)

The following SELECT statement uses dot notation to access field length of the rect column:

SELECT rect.length FROM rectangles

	WHERE area = 64

If you want to select all fields of a column that has a row type, you can specify the column name without dot notation. For example, you can select all fields of the rect column as follows:

SELECT rect FROM rectangles

	WHERE area = 64

You can also use asterisk notation to project all the fields of a column that has a row type. For example, if you want to use asterisk notation to select all fields of the rect column, you can enter the following statement:

SELECT rect.* FROM rectangles

	WHERE area = 64

Asterisk notation is a shorthand form of dot notation that is easier than specifying each field of the rect column individually:

SELECT rect.x, rect.y, rect.length,

	rect.width

	FROM rectangles

	WHERE area = 64

Asterisk notation is not necessary with row-type columns because you can specify just the column name itself to project all of its fields. However, asterisk notation is quite helpful with row-type expressions such as subqueries and user-defined functions that return row-type values. For further information see Using Dot Notation with Row-Type Expressions.

You can use asterisk notation with columns and expressions of row type in the select list of a SELECT statement only. You cannot use asterisk notation with columns and expressions of row type in any other clause of a SELECT statement.

When the row type that defines a column itself contains other row types, the column contains nested fields. You use dot notation to access these nested fields within a column. For example, assume that the address column of the employee table contains the fields street, city, state, and zip. In addition, the zip field contains the nested fields: z_code and z_suffix. A query on the zip field returns values for the z_code and z_suffix fields. However, you can specify that a query returns only specific nested fields. The following example shows how to use dot notation to construct a SELECT statement that returns rows for the z_code field of the address column only.

SELECT address.zip.z_code 

	FROM employee 

The database server uses the following precedence rules to interpret dot notation:

1. schema name_a . table name_b . column name_c . field name_d
2. table name_a . column name_b . field name_c . field name_d
3. column name_a . field name_b . field name_c . field name_d

When the meaning of a particular identifier is ambiguous, the database server uses precedence rules to determine which database object the identifier specifies. Consider the following two tables:

CREATE TABLE b (c ROW(d INTEGER, e CHAR(2));
CREATE TABLE c (d INTEGER);

In the following SELECT statement, the expression c.d references column d of table c (rather than field d of column c in table b) because a table identifier has a higher precedence than a column identifier:

SELECT * 
FROM b,c 
WHERE c.d = 10

For more information about precedence rules and how to use dot notation with row columns, see the Informix Guide to SQL: Tutorial.

You can use dot notation whenever a column has a row data type. However, in addition to column expressions, you can use dot notation with any expression that evaluates to a row type. For example, you can use dot notation in a subquery in an INSERT statement if the subquery returns a single row of values. Assume that you have created a row type named row_t:

CREATE ROW TYPE row_t (part_id INT, amt INT)

Also assume that you have created a typed table named tab1 that is based on the row_t row type:

CREATE TABLE tab1 OF TYPE row_t

Assume also that you have inserted the following values into table tab1:

INSERT INTO tab1 VALUES (ROW(1,7));

INSERT INTO tab1 VALUES (ROW(2,10));

Finally, assume that you have created another table named tab2:

CREATE TABLE tab2 (colx INT)

Now you can use dot notation to insert the value from just the part_id column of table tab1 into the tab2 table:

INSERT INTO tab2

	VALUES ((SELECT t FROM tab1 t

		WHERE part_id = 1).part_id)

The asterisk form of dot notation is not necessary when you want to select all fields of a row type column because you can just specify the column name itself to select all of its fields. However, the asterisk form of dot notation can be quite helpful when you use a subquery as in the preceding example or when you call a user-defined function to return row type values.

Suppose that you create a user-defined function named new_row that returns row type values, and you want to call this function to insert the row type values into a table. Asterisk notation makes it easy to specify that all the row type values produced by the new_row function are to be inserted into the table:

INSERT INTO mytab2 SELECT new_row (mycol).* FROM mytab1

References to the fields of a row-type column or a row-type expression are not allowed in fragment expressions. A fragment expression is an expression that defines a table fragment or an index fragment in statements like CREATE TABLE, CREATE INDEX, and ALTER FRAGMENT.

You can use subscripts on CHAR, VARCHAR, NCHAR, NVARCHAR, BYTE, and TEXT columns. The subscripts indicate the starting and ending character positions that are contained in the expression. Together the column subscripts define a column substring. The column substring is the portion of the column that is contained in the expression.

For example, if a value in the lname column of the customer table is Greenburg, the following expression evaluates to burg:

lname[6,9]

For information on the GLS aspects of column subscripts and substrings, see the Informix Guide to GLS Functionality.

In Dynamic Server, you can use the rowid column that is associated with a table row as a property of the row. The rowid column is essentially a hidden column in nonfragmented tables and in fragmented tables that were created with the WITH ROWIDS clause. The rowid column is unique for each row, but it is not necessarily sequential. Informix recommends, however, that you use primary keys as an access method rather than exploiting the rowid column.

The following examples show possible uses of the ROWID keyword in a SELECT statement:

SELECT *, ROWID FROM customer



SELECT fname, ROWID FROM customer

ORDER BY ROWID 



SELECT HEX(rowid) FROM customer

WHERE customer_num = 106

The last SELECT statement example shows how to get the page number (the first six digits after 0x) and the slot number (the last two digits) of the location of your row.

You cannot use the ROWID keyword in the select list of a query that contains an aggregate function.

The SELECT, UPDATE, and INSERT statements do not manipulate the values of smart large objects directly. Instead, they use a handle value, which is a type of pointer, to access the BLOB or CLOB value, as follows:

• The SELECT statement returns a handle value to the BLOB or CLOB value that the select list specifies.

SELECT does not return the actual data for the BLOB or CLOB column that the select list specifies. Instead, it returns a handle value to the column data.

• The INSERT and UPDATE statements accept a handle value for a BLOB or CLOB to be inserted or updated.

INSERT and UPDATE do not send the actual data for the BLOB or CLOB column to the database server. Instead, they accept a handle value to this data as the column value.

To access the data of a smart-large-object column, you must use one of the following application programming interfaces (APIs):

• From within an Informix ESQL/C program, use the ESQL/C library functions that access smart large objects.

For more information, see the Informix ESQL/C Programmer's Manual.

• From within a C program such as a DataBlade module, use the Client and Server API.

For more information, see your DataBlade Developers Kit User's Guide.

You cannot use the name of a smart-large-object column in expressions that involve arithmetic operators. For example, operations such as addition or subtraction on the smart-large-object handle value have no meaning.

When you select a smart-large-object column, you can assign the handle value to any number of columns: all columns with the same handle value share the CLOB or BLOB value across several columns. This storage arrangement reduces the amount of disk space that the CLOB or BLOB data takes. However, when several columns share the same smart-large-object value, the following conditions result:

• The chance of lock contention on a CLOB or BLOB column increases.

If two columns share the same smart-large-object value, the data might be locked by either column that needs to access it.

• The CLOB or BLOB value can be updated from a number of points

To remove these constraints, you can create separate copies of the BLOB or CLOB data for each column that needs to access it. You can use the LOCOPY function to create a copy of an existing smart large object. You can also use the built-in functions LOTOFILE, FILETOCLOB, and FILETOBLOB to access smart-large-object values. For more information on these functions, see Smart-Large-Object Functions. For more information on the BLOB and CLOB data types, see the Informix Guide to SQL: Reference.

Conditional expressions return values that depend on the outcome of conditional tests. The following diagram shows the syntax for Conditional Expressions.

The CASE expression allows an SQL statement such as the SELECT statement to return one of several possible results, depending on which of several condition tests evaluates to true. The CASE expression has two forms as the following diagram shows: generic CASE expressions and linear CASE expressions.

You can use a generic or linear CASE expression wherever you can use a column expression in an SQL statement (for example, in the select list of a SELECT statement.) You must include at least one WHEN clause in the CASE expression. Subsequent WHEN clauses and the ELSE clause are optional.

The expressions in the search condition or the result value expression can contain subqueries.

You can nest a CASE expression in another CASE expression.

When a CASE expression appears in an aggregate expression, you cannot use aggregate functions in the CASE expression.

A generic CASE expression tests for a true condition in a WHEN clause and when it finds a true condition it returns the result specified in the THEN clause.

The database server processes the WHEN clauses in the order that they appear in the statement. As soon as the database server finds a WHEN clause whose search condition evaluates to true, it takes the corresponding result value expression as the overall result of the CASE expression, and it stops processing the CASE expression.

If no WHEN condition evaluates to true, the database server takes the result of the ELSE clause as the overall result. If no WHEN condition evaluates to true, and no ELSE clause was specified, the resulting value is null. You can use the IS NULL condition to handle null results. For information on how to handle null values, see IS NULL Condition.

The following example shows the use of a generic CASE expression in the select list of a SELECT statement. In this example the user retrieves the name and address of each customer as well as a calculated number that is based on the number of problems that exist for that customer.

SELECT cust_name,

	CASE

	WHEN number_of_problems = 0

		THEN 100

	WHEN number_of_problems > 0 AND number_of_problems < 4

		THEN number_of_problems * 500

	WHEN number_of_problems >= 4 and number_of_problems <= 9

		THEN number_of_problems * 400

	ELSE

		(number_of_problems * 300) + 250

	END,

	cust_address

FROM custtab

In a generic CASE expression, all the results should be of the same type, or they should evaluate to a common compatible type. If the results in all the WHEN clauses are not of the same type, or if they do not evaluate to values of mutually compatible types, an error occurs.

Linear CASE Expressions

A linear CASE expression tests for a match between the value expression that follows the CASE keyword and a value expression in a WHEN clause.

First the database server evaluates the value expression that follows the CASE keyword. Then the database server processes the WHEN clauses in the order that they appear in the CASE expression. As soon as the database server finds a WHEN clause where the value expression after the WHEN keyword evaluates to the same value as the value expression that follows the CASE keyword, it takes the value expression that follows the THEN keyword as the overall result of the CASE expression. Then the database server stops processing the CASE expression.

If none of the value expressions that follow the WHEN keywords evaluates to the same value as the value expression that follows the CASE keyword, the database server takes the result value expression of the ELSE clause as the overall result of the CASE expression. If all of the value expressions that follow the WHEN keyword in all the WHEN clauses do not evaluate to the same value as the value expression that follows the CASE keyword, and the user did not specify an ELSE clause, the resulting value is null.

The following example shows a linear CASE expression in the select list of a SELECT statement. For each movie in a table of movie titles, the SELECT statement displays the title of the movie, the cost of the movie, and the type of movie. The statement uses a CASE expression to derive the type of each movie.

SELECT title,

	CASE movie_type

		WHEN 1 THEN 'HORROR'

		WHEN 2 THEN 'COMEDY'

		WHEN 3 THEN 'ROMANCE'

		WHEN 4 THEN 'WESTERN'

		ELSE 'UNCLASSIFIED'
	END,

	our_cost

FROM movie_titles

In linear CASE expressions, the types of value expressions in all the WHEN clauses have to be compatible with the type of the value expression that follows the CASE keyword.

The NVL expression returns different results depending on whether its first argument evaluates to null.

NVL evaluates expression1. If expression1 is not null, NVL returns the value of expression1. If expression1 is null, NVL returns the value of expression2. The expressions expression1 and expression2 can be of any data type, as long as they evaluate to a common compatible type.

Suppose that the addr column of the employees table has null values in some rows, and the user wants to be able to print the label Address unknown for these rows. The user enters the following SELECT statement to display the label Address unknown when the addr column has a null value.

SELECT fname, NVL (addr, 'Address unknown') AS address
	FROM employees

The DECODE expression is similar to the CASE expression in that it can print different results depending on the values found in a specified column.

The expressions expr, when_expr, and then_expr are required. DECODE evaluates expr and compares it to when_expr. If the value of when_expr matches the value of expr, DECODE returns then_expr.

The expressions when_expr and then_expr are an expression pair, and you can specify any number of expression pairs in the DECODE function. In all cases, DECODE compares the first member of the pair against expr and returns the second member of the pair if the first member matches expr.

If no expression matches expr, DECODE returns else_expr. However, if no expression matches expr and the user did not specify else_expr, DECODE returns NULL.

You can specify any data type as input, but two limitations exist.

• All occurrences of the parameter when_expr must have the same data type, or they must evaluate to a common compatible type. Similarly, all occurrences of when_expr must have the same data type as expr, or they must evaluate to a common compatible type.
• All occurrences of the parameter then_expr must have the same data type, or they must evaluate to a common compatible type. Similarly, all occurrences of then_expr must have the same data type as else_expr, or they must evaluate to a common compatible type.

Suppose that a user wants to convert descriptive values in the evaluation column of the students table to numeric values in the output. The following table shows the contents of the students table.

The user now enters a SELECT statement with the DECODE function to convert the descriptive values in the evaluation column to numeric equivalents.

SELECT firstname, DECODE(evaluation,

	'Poor', 0,

	'Fair', 25, 

	'Good', 50, 

	'Very Good', 75,

	'Great', 100, 

	-1) as grade
FROM students

The following table shows the output of this SELECT statement.

The following diagram shows the possible syntax for constant expressions.

Quoted String as an Expression

The following examples show quoted strings as expressions:

SELECT 'The first name is ', fname FROM customer



INSERT INTO manufact VALUES ('SPS', 'SuperSport')



UPDATE cust_calls SET res_dtime = '1997-1-1 10:45' 

	WHERE customer_num = 120 AND call_code = 'B'

For more information, see Quoted String.

The following examples show literal numbers as expressions:

INSERT INTO items VALUES (4, 35, 52, 'HRO', 12, 4.00)



INSERT INTO acreage VALUES (4, 5.2e4)



SELECT unit_price + 5 FROM stock



SELECT -1 * balance FROM accounts

For more information, see Literal Number.

The USER function returns a string that contains the login name of the current user (that is, the person running the process).

The following statements show how you might use the USER function:

INSERT INTO cust_calls VALUES 

   (221,CURRENT,USER,'B','Decimal point off', NULL, NULL)



SELECT * FROM cust_calls WHERE user_id = USER



UPDATE cust_calls SET user_id = USER WHERE customer_num = 220

The USER function does not change the case of a user ID. If you use USER in an expression and the present user is Robertm, the USER function returns Robertm, not robertm.

If you specify USER as the default value for a column, the column must have a CHAR, VARCHAR, NCHAR, or NVARCHAR data type.

If you specify USER as the default value for a column, Informix recommends that the size of the column be at least 32 bytes long. You risk getting an error message during INSERT and ALTER TABLE operations if the length of the column is too small to store the default value.

If you specify USER as the default value for a column, Informix recommends that the size of the column be at least 8 bytes long. You risk getting an error message during INSERT and ALTER TABLE operations if the length of the column is too small to store the default value.

In an ANSI-compliant database, if you do not use quotes around the owner name, the name of the table owner is stored as uppercase letters. If you use the USER keyword as part of a condition, you must be sure that the way the user name is stored agrees with the values that the USER function returns, with respect to case.

The DBSERVERNAME function returns the database server name, as defined in the ONCONFIG file for the installation where the current database resides or as specified in the INFORMIXSERVER environment variable. The two function names, DBSERVERNAME and SITENAME are synonymous. You can use the DBSERVERNAME function to determine the location of a table, to put information into a table, or to extract information from a table. You can insert DBSERVERNAME into a simple character field or use it as a default value for a column.

If you specify DBSERVERNAME as a default value for a column, the column must have a CHAR, VARCHAR, NCHAR, or NVARCHAR data type.

If you specify DBSERVERNAME as the default value for a column, Informix recommends that the size of the column be at least 128 bytes long. You risk getting an error message during INSERT and ALTER TABLE operations if the length of the column is too small to store the default value.

If you specify DBSERVERNAME as the default value for a column, Informix recommends that the size of the column be at least 18 bytes long. You risk getting an error message during INSERT and ALTER TABLE operations if the length of the column is too small to store the default value.

In the following example, the first statement returns the name of the database server where the customer table resides. Because the query is not restricted with a WHERE clause, it returns DBSERVERNAME for every row in the table. If you add the DISTINCT keyword to the SELECT clause, the query returns DBSERVERNAME once. The second statement adds a row that contains the current site name to a table. The third statement returns all the rows that have the site name of the current system in site_col. The last statement changes the company name in the customer table to the current system name.

SELECT DBSERVERNAME FROM customer



INSERT INTO host_tab VALUES ('1', DBSERVERNAME)



SELECT * FROM host_tab WHERE site_col = DBSERVERNAME



UPDATE customer SET company = DBSERVERNAME

	 WHERE customer_num = 120

Use the TODAY function to return the system date as a DATE data type. If you specify TODAY as a default value for a column, it must be a DATE column.

The following examples show how you might use the TODAY function in an INSERT, UPDATE, or SELECT statement:

UPDATE orders (order_date) SET order_date = TODAY

	 WHERE order_num = 1005 



INSERT INTO orders VALUES 

	(0, TODAY, 120, NULL, N, '1AUE217', NULL, NULL, NULL, 
NULL)



SELECT * FROM orders WHERE ship_date = TODAY

The CURRENT function returns a DATETIME value with the date and time of day, showing the current instant.

If you do not specify a datetime qualifier, the default qualifier is YEAR TO FRACTION(3). The USEOSTIME configuration parameter specifies whether or not the database server uses subsecond precision when it obtains the current time from the operating system. For more information on the USEOSTIME configuration parameter, see your Administrator's Reference.

You can use the CURRENT function in any context in which you can use a literal DATETIME (see Literal DATETIME). If you specify CURRENT as the default value for a column, it must be a DATETIME column and the qualifier of CURRENT must match the column qualifier, as the following example shows:

CREATE TABLE new_acct (col1 int, col2 DATETIME YEAR TO DAY

	DEFAULT CURRENT YEAR TO DAY)

If you use the CURRENT keyword in more than one place in a single statement, identical values can be returned at each point of the call. You cannot rely on the CURRENT function to provide distinct values each time it executes.

The returned value comes from the system clock and is fixed when any SQL statement starts. For example, any call to CURRENT from inside the SPL function that an EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement names returns the value when the SPL function starts.

The CURRENT function is always evaluated in the database server where the current database is located. If the current database is in a remote database server, the returned value is from the remote host.

The CURRENT function might not execute in the physical order in which it appears in a statement. You should not use the CURRENT function to mark the start, end, or a specific point in the execution of a statement.

If your platform does not provide a system call that returns the current time with subsecond precision, the CURRENT function returns a zero for the FRACTION field.

In the following example, the first statement uses the CURRENT function in a WHERE condition. The second statement uses the CURRENT function as the input for the DAY function. The last query selects rows whose call_dtime value is within a range from the beginning of 1997 to the current instant.

DELETE FROM cust_calls WHERE 

	res_dtime < CURRENT YEAR TO MINUTE



SELECT * FROM orders WHERE DAY(ord_date) < DAY(CURRENT)



SELECT * FROM cust_calls WHERE call_dtime

	BETWEEN '1997-1-1 00:00:00' AND CURRENT

For more information, see DATETIME Field Qualifier.

The following examples show literal DATETIME as an expression:

SELECT DATETIME (1997-12-6) YEAR TO DAY FROM customer



UPDATE cust_calls SET res_dtime = DATETIME (1998-07-07 10:40)

			YEAR TO MINUTE

	WHERE customer_num = 110

	AND call_dtime = DATETIME (1998-07-07 10:24) YEAR TO 
MINUTE



SELECT * FROM cust_calls 

	WHERE call_dtime

 	= DATETIME (1998-12-25 00:00:00) YEAR TO SECOND

For more information, see Literal DATETIME.

The following examples show literal INTERVAL as an expression:

INSERT INTO manufact VALUES ('CAT', 'Catwalk Sports', 

	INTERVAL (16) DAY TO DAY)



SELECT lead_time + INTERVAL (5) DAY TO DAY FROM manufact

The second statement in the preceding example adds five days to each value of lead_time selected from the manufact table.

For more information, see Literal INTERVAL.

The UNITS keyword enables you to display a simple interval or increase or decrease a specific interval or datetime value.

If n is not an integer, it is rounded down to the nearest whole number when it is used.

In the following example, the first SELECT statement uses the UNITS keyword to select all the manufacturer lead times, increased by five days. The second SELECT statement finds all the calls that were placed more than 30 days ago. If the expression in the WHERE clause returns a value greater than 99 (maximum number of days), the query fails. The last statement increases the lead time for the ANZA manufacturer by two days.

SELECT lead_time + 5 UNITS DAY FROM manufact



SELECT * FROM cust_calls 

	WHERE (TODAY - call_dtime) > 30 UNITS DAY



UPDATE manufact SET lead_time = 2 UNITS DAY + lead_time

	WHERE manu_code = 'ANZ'

The following examples show literal collections as expressions:

INSERT INTO tab_a (set_col) VALUES ("SET{6, 9, 3, 12, 4}")



INSERT INTO TABLE(a_set) VALUES (9765)



UPDATE table1 SET set_col = "LIST{3}"



SELECT set_col FROM table1

	WHERE SET{17} IN (set_col)

For more information, see Literal Collection. For syntax that allows you to use expressions that evaluate to element values, see Collection Constructors.

The following examples show literal rows as expressions:

INSERT INTO employee VALUES  

	(ROW('103 Baker St',  'San Francisco', 

		'CA', 94500))



UPDATE rectangles

	SET rect = ROW(8, 3, 7, 20)

	WHERE area = 140



EXEC SQL update table(:a_row) 

	set x=0, y=0, length=10, width=20;



SELECT row_col FROM tab_b

	WHERE ROW(17, 'abc') IN (row_col)

For more information, see Literal Row. For syntax that allows you to use expressions that evaluate to field values, see ROW Constructors.

A constructor is a function that the database server uses to create an instance of a particular data type. The database server supports ROW and collection constructors.

You use ROW constructors to generate values for row-type columns. Suppose you create the following named row type and a table that contains the named row type row_t and an unnamed row type:

CREATE ROW TYPE row_t ( x INT, y INT);
CREATE TABLE new_tab 
(
col1 row_t, 
col2 ROW( a CHAR(2), b INT
)

When you define a column as a named row type or unnamed row type, you must use a ROW constructor to generate values for the row column. To create a value for either a named row type or unnamed row type, you must complete the following steps:

• Begin the expression with the ROW keyword.
• Specify a value for each field of the row type.
• Enclosed the field values within parentheses.

The format of the value for each field must be compatible with the data type of the row field to which it is assigned.

You can use any kind of expression as a value with a ROW constructor, including literals, functions, and variables. The following examples show the use of different types of expressions with ROW constructors to specify values:

ROW(5, 6.77, 'HMO')


ROW(col1.lname, 45000)


ROW('john davis', TODAY)


ROW(USER, SITENAME)

The following statement uses literal numbers and quoted strings with ROW constructors to insert values into col1 and col2 of the new_tab table:

INSERT INTO new_tab 
VALUES 
(
ROW(32, 65)::row_t,
ROW('CA', 34)
)

When you use a ROW constructor to generate values for a named row type, you must explicitly cast the row value to the appropriate named row type. The cast is necessary to generate a value of the named row type. To cast the row value as a named row type, you can use the cast operator (::) or the CAST AS keywords, as shown in the following examples:

ROW(4,5)::row_t
CAST (ROW(3,4) AS row_t)

You can use a ROW constructor to generate row type values not only in INSERT and UPDATE statements but also in SELECT statements. In the following example, the WHERE clause of a SELECT statement specifies a row type value that is cast as type person_t

SELECT * 
FROM person_tab
WHERE col1 = ROW('charlie','hunter')::person_t

For further information on using ROW constructors in INSERT and UPDATE statements, see the INSERT and UPDATE statements in this manual. For information on named row types, see the CREATE ROW TYPE statement. For information on unnamed row types, see the discussion of the ROW data type in the Informix Guide to SQL: Reference. For task-oriented information on named row types and unnamed row types, see the Informix Guide to Database Design and Implementation.

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.

The following table differentiates the types of collections that you can construct.

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 arbitrary 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 ({}).

The element type of the collection can be any built-in or extended data type.

Elements of a collection cannot be null, therefore if an expression element evaluates to a null value the database server returns an error.

The element type of each expression must be homogeneous, that is, they must be exactly the same type. This can be accomplished by casting the entire collection constructor expression to a collection type, or by casting individual element expressions to the same type.

If the database server cannot determine the collection type and the element types are not homogeneous, then the collection constructor will return an error. In the case of host variables, this determination is made at bind time when the client informs the database server the element type of the host variable.

The following extended example illustrates that you can construct collection with many various expressions as long as the resulting values are of the same 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 example assumes that a cast from BOOLEAN to INT exists.

For information about a more restrictive, but still-supported syntax for how to specify values for a collection column, see Literal Collection.

A function expression can call built-in functions or 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)

An algebraic function takes one or more arguments, as the following diagram shows.

The ABS function gives the absolute value for a given expression. The function requires a single numeric argument. The value returned is the same as the argument type. The following example shows all orders of more than $20 paid in cash (+) or store credit (-). The stores_demo database does not contain any negative balances; however, you might have negative balances in your applii class="bs-bullet-sub">Set file destination to client to identify the client computer as the location of the source file. The pathname can be either a full pathname or relative to the current directory.

The table and column parameters are optional:

• If you omit table and column, the FILETOBLOB function creates a BLOB value with the system-specified storage defaults, and the FILETOCLOB function creates a CLOB value with the system-specified storage defaults.

These functions obtain the system-specific storage characteristics from either the ONCONFIG file or the sbspace. For more information on system-specified storage defaults, see the Administrator's Guide.

• If you specify table and column, the FILETOBLOB and FILETOCLOB functions use the storage characteristics from the specified column for the BLOB or CLOB value that they create.

The FILETOBLOB function returns a handle value (a pointer) to the new BLOB value. Similarly, the FILETOCLOB function returns a handle value to the new CLOB value. Neither of these functions actually stores the smart-large-object value into a column in the database. You must assign the BLOB or CLOB value to the appropriate column.

The FILETOCLOB function performs any code-set conversion that might be required when it copies the file from the client or server computer to the database.

The following INSERT statement uses the FILETOCLOB function to create a CLOB value from the value in the haven.rsm file:

INSERT INTO candidate (cand_num, cand_lname, resume) 

	VALUES (0, 'Haven', FILETOCLOB('haven.rsm', 'client'))

In the preceding example, the FILETOCLOB function reads the haven.rsm file in the current directory on the client computer and returns a handle value to a CLOB value that contains the data in this file. Because the FILETOCLOB function does not specify a table and column name, this new CLOB value has the system-specified storage characteristics. The INSERT statement then assigns this CLOB value to the resume column in the candidate table.

The LOTOFILE function copies a smart large object to an operating-system file. The first parameter specifies the BLOB or CLOB column to copy. The function determines the operating-system file to create from the following parameters:

• The pathname parameter identifies the directory path and name of the source file.
• The file destination parameter identifies the computer, client or server, on which this file resides:
• Set file destination to client to identify the client computer as the location of the source file. The pathname can be either a full pathname or relative to the current directory.
• Set file destination to server to identify the server computer as the location of the source file. The pathname must be a full pathname.

By default, the LOTOFILE function generates a filename of the form:

file.hex_id

In this format, file is the filename you specify in pathname and hex_id is the unique hexadecimal smart-large-object identifier. The maximum number of digits for a smart-large-object identifier is 17; however most smart large objects would have an identifier with significantly fewer digits.

For example, suppose you specify a pathname value as follows:

'/tmp/resume'

If the CLOB column has an identifier of 203b2, the LOTOFILE function would create the file:

/tmp/resume.203b2   

For example, suppose you specify a pathname value as follows:

'C:\tmp\resume'

If the CLOB column has an identifier of 203b2, the LOTOFILE function would create the file:

C:\tmp\resume.203b2   

To change the default filename, you can specify the following wildcards in the filename of the pathname:

• One or more contiguous question mark (?) characters in the filename can generate a unique filename.

The LOTOFILE function replaces each question mark with a hexadecimal digit from the identifier of the BLOB or CLOB column.

For example, suppose you specify a pathname value as follows:

'/tmp/resume??.txt'

The LOTOFILE function puts 2 digits of the hexadecimal identifier into the name. If the CLOB column has an identifier of 203b2, the LOTOFILE function would create the file:

/tmp/resume20.txt

If you specify more than 17 question marks, the LOTOFILE function ignores them.

• An exclamation point (!) at the end of the filename indicates that the filename does not need to be unique.

For example, suppose you specify a pathname value as follows:

'C:\tmp\resume.txt!'

The LOTOFILE function does not use the smart-large-object identifier in the filename so it generates the following file:

C:\tmp\resume.txt

If the filename you specify already exists, LOTOFILE returns an error.

The LOTOFILE function performs any code-set conversion that might be required when it copies a CLOB value from the database to a file on the client or server computer.

The LOCOPY function creates a copy of a smart large object. The first parameter specifies the BLOB or CLOB column to copy. The table and column parameters are optional:

• If you omit table and column, the LOCOPY function creates a smart large object with system-specified storage defaults and copies the data in the BLOB or CLOB column into it.

The LOCOPY function obtains the system-specific storage defaults from either the ONCONFIG file or the sbspace. For more information on system-specified storage defaults, see the Administrator's Guide.

• When you specify table and column, the LOCOPY function uses the storage characteristics from the specified column for the BLOB or CLOB value that it creates.

The LOCOPY function returns a handle value (a pointer) to the new BLOB or CLOB value. This function does not actually store the new smart-large-object value into a column in the database. You must assign the BLOB or CLOB value to the appropriate column.

The following ESQL/C code fragment copies the CLOB value in the resume column of the candidate table to the resume column of the interview table:

/* Insert a new row in the interviews table and get the
  * resulting SERIAL value (from sqlca.sqlerrd[1])
  */
EXEC SQL insert into interviews (intrv_num, intrv_time)

	values (0, '09:30');

intrv_num = sqlca.sqlerrd[1];

/* Update this interviews row with the candidate number

  * and resume from the candidate table. Use LOCOPY to

  * create a copy of the CLOB value in the resume column

  * of the candidate table.
  */
EXEC SQL update interviews 

	SET (cand_num, resume) = 

		(SELECT cand_num, 

			LOCOPY(resume, 'candidate', 'resume')

		FROM candidate

		WHERE cand_lname = 'Haven')

	WHERE intrv_num = :intrv_num;

In the preceding example, the LOCOPY function returns a handle value for the copy of the CLOB resume column in the candidate table. Because the LOCOPY function specifies a table and column name, this new CLOB value has the storage characteristics of this resume column. If you omit the table (candidate) and column (resume) names, the LOCOPY function uses the system-defined storage defaults for the new CLOB value. The UPDATE statement then assigns this new CLOB value to the resume column in the interviews table.

The DATE function returns a DATE value that corresponds to the non-date expression with which you call it. The argument can be any expression that can be converted to a DATE value, usually a CHAR, DATETIME, or INTEGER value. The following WHERE clause specifies a CHAR value for the non-date expression:

WHERE order_date < DATE('12/31/97')

When the DATE function interprets a CHAR non-date expression, it expects this expression to conform to any DATE format that the DBDATE environment specifies. For example, suppose DBDATE is set to Y2MD/ when you execute the following query:

SELECT DISTINCT DATE('02/01/1998') FROM ship_info

This SELECT statement generates an error because the DATE function cannot convert this non-date expression. The DATE function interprets the first part of the date string (02) as the year and the second part (01) as the month. For the third part (1998), the DATE function encounters four digits when it expects a two-digit day (valid day values must be between 01 and 31). It therefore cannot convert the value. For the SELECT statement to execute successfully with the Y2MD/ value for DBDATE, the non-date expression would need to be '98/02/01'. For information on the format of DBDATE, see the Informix Guide to SQL: Reference.

When you specify a positive INTEGER value for the non-date expression, the DATE function interprets the value as the number of days after the default date of December 31, 1899. If the integer value is negative, the DATE function interprets the value as the number of days before December 31, 1899. The following WHERE clause specifies an INTEGER value for the non-date expression:

WHERE order_date  <  DATE(365)

The database server searches for rows with an order_date value less than December 31, 1900 (12/31/1899 plus 365 days).

The DAY function returns an integer that represents the day of the month.
The following example uses the DAY function with the CURRENT function to compare column values to the current day of the month:

WHERE DAY(order_date) > DAY(CURRENT)

The MONTH function returns an integer that corresponds to the month portion of its type DATE or DATETIME argument. The following example returns a number from 1 through 12 to indicate the month when the order was placed:

SELECT order_num, MONTH(order_date) FROM orders

The WEEKDAY function returns an integer that represents the day of the week; zero (0) represents Sunday, one represents Monday, and so on. The following lists all the orders that were paid on the same day of the week, which is the current day:

SELECT * FROM orders

	WHERE WEEKDAY(paid_date) = WEEKDAY(CURRENT)

The YEAR function returns a four-digit integer that represents the year. The following example lists orders in which the ship_date is earlier than the beginning of the current year:

SELECT order_num, customer_num FROM orders

	WHERE year(ship_date) < YEAR(TODAY)

Similarly, because a DATE value is a simple calendar date, you cannot add or subtract a DATE value with an INTERVAL value whose last qualifier is smaller than DAY. In this case, convert the DATE value to a DATETIME value.

The EXTEND function adjusts the precision of a DATETIME or DATE value. The expression cannot be a quoted string representation of a DATE value.

If you do not specify first and last qualifiers, the default qualifiers are YEAR TO FRACTION(3).

If the expression contains fields that are not specified by the qualifiers, the unwanted fields are discarded.

If the first qualifier specifies a larger (that is, more significant) field than what exists in the expression, the new fields are filled in with values returned by the CURRENT function. If the last qualifier specifies a smaller field (that is, less significant) than what exists in the expression, the new fields are filled in with constant values. A missing MONTH or DAY field is filled in with 1, and the missing HOUR to FRACTION fields are filled in with 0.

In the following example, the first EXTEND call evaluates to the call_dtime column value of YEAR TO SECOND. The second statement expands a literal DATETIME so that an interval can be subtracted from it. You must use the EXTEND function with a DATETIME value if you want to add it to or subtract it from an INTERVAL value that does not have all the same qualifiers. The third example updates only a portion of the datetime value, the hour position. The EXTEND function yields just the hh:mm part of the datetime. Subtracting 11:00 from the hours and minutes of the datetime yields an INTERVAL value of the difference, plus or minus, and subtracting that from the original value forces the value to 11:00.

EXTEND (call_dtime, YEAR TO SECOND)



EXTEND (DATETIME (1989-8-1) YEAR TO DAY, YEAR TO MINUTE)

	- INTERVAL (720) MINUTE (3) TO MINUTE



UPDATE cust_calls SET call_dtime = call_dtime -

(EXTEND(call_dtime, HOUR TO MINUTE) - DATETIME (11:00) HOUR 

TO MINUTE) WHERE customer_num = 106

The MDY function returns a type DATE value with three expressions that evaluate to integers representing the month, day, and year. The first expression must evaluate to an integer representing the number of the month (1 to 12).

The second expression must evaluate to an integer that represents the number of the day of the month (1 to 28, 29, 30, or 31, as appropriate for the month.)

The third expression must evaluate to a four-digit integer that represents the year. You cannot use a two-digit abbreviation for the third expression. The following example sets the paid_date associated with the order number 8052 equal to the first day of the present month:

UPDATE orders SET paid_date = MDY(MONTH(TODAY), 1, 
YEAR(TODAY))

	WHERE po_num = '8052'

The TO_CHAR function converts a DATE or DATETIME value to a character string. The character string contains the date that was specified in the source_date parameter, and represents this date in the format that was specified in the format_string parameter.

You can use this function only with built-in data types.

If the value of the source_date parameter is null, the result of the function is a null value.

If you omit the format_string parameter, the TO_CHAR function uses the default date format to format the character string. The default date format is specified by environment variables such as GL_DATETIME and GL_DATE.

The format_string parameter does not have to imply the same qualifiers as the source_date parameter. When the implied formatting mask qualifier in format_ string is different from the qualifier in source_date, the TO_CHAR function extends the DATETIME value as if it had called the EXTEND function.

In the following example, the user wants to convert the begin_date column of the tab1 table to a character string. The begin_date column is defined as a DATETIME YEAR TO SECOND data type. The user uses a SELECT statement with the TO_CHAR function to perform this conversion.

SELECT TO_CHAR(begin_date, '%A %B %d, %Y %R')

	FROM tab1

The symbols in the format_string parameter in this example have the following meanings. For a complete list of format symbols and their meanings, see the GL_DATE and GL_DATETIME environment variables in the Informix Guide to GLS Functionality.

The result of applying the specified format_string to the begin_date column is as follows:

Wednesday July 23, 1997 18:45

The TO_DATE function converts a character string to a DATETIME value. The function evaluates the char_expression parameter as a date according to the date format you specify in the format_string parameter, and returns the equivalent date.

You can use this function only with built-in data types.

If the value of the char_expression parameter is null, the result of the function is a null value.

If you omit the format_string parameter, the TO_DATE function applies the default DATETIME format to the DATETIME value. The default DATETIME format is specified by the GL_DATETIME environment variable.

In the following example, the user wants to convert a character string to a DATETIME value in order to update the begin_date column of the tab1 table with the converted value. The begin_date column is defined as a DATETIME YEAR TO SECOND data type. The user uses an UPDATE statement that contains a TO_DATE function to accomplish this result.

UPDATE tab1

	SET begin_date = TO_DATE('Wednesday July 23, 1997 18:45',

	'%A %B %d, %Y %R');

The format_string parameter in this example tells the TO_DATE function how to format the converted character string in the begin_date column. For a table that shows the meaning of each format symbol in this format string, see TO_CHAR Function.

A trigonometric function takes an argument, as the following diagram shows.

The COS, SIN, and TAN functions take the number of radians (radian_expr) as an argument.

If you are using degrees and want to convert degrees to radians, use the following formula:

# degrees * p/180= # radians

# radians * 180/p = # degrees

The COS function returns the cosine of a radian expression. The following example returns the cosine of the values of the degrees column in the anglestbl table. The expression passed to the COS function in this example converts degrees to radians.

SELECT COS(degrees*180/3.1416) FROM anglestbl

The SIN function returns the sine of a radian expression. The following example returns the sine of the values in the radians column of the anglestbl table:

SELECT SIN(radians) FROM anglestbl

The TAN function returns the tangent of a radian expression. The following example returns the tangent of the values in the radians column of the anglestbl table:

SELECT TAN(radians) FROM anglestbl

The ACOS function returns the arc cosine of a numeric expression. The following example returns the arc cosine of the value (-0.73) in radians:

SELECT ACOS(-0.73) FROM anglestbl

The ASIN function returns the arc sine of a numeric expression. The following example returns the arc sine of the value (-0.73) in radians:

SELECT ASIN(-0.73) FROM anglestbl

The ATAN function returns the arc tangent of a numeric expression. The following example returns the arc tangent of the value (-0.73) in radians:

SELECT ATAN(-0.73) FROM anglestbl

The ATAN2 function computes the angular component of the polar coordinates (r, ) associated with (x, y). The following example compares angles to for the rectangular coordinates (4, 5):

WHERE angles > ATAN2(4,5)     --determines  for (4,5) and

                                compares to angles

You can determine the length of the radial coordinate r using the expression shown in the following example:

SQRT(POW(x,2) + POW(y,2))     --determines r for (x,y)

You can determine the length of the radial coordinate r for the rectangular coordinates (4,5) using the expression shown in the following example:

SQRT(POW(4,2) + POW(5,2))     --determines r for (4,5)

String-manipulation functions perform various operations on strings of characters. The syntax for string-manipulation functions is as follows.

Use the TRIM function to remove leading or trailing (or both) pad characters from a string.

The TRIM function returns a VARCHAR string that is identical to the character string passed to it, except that any leading or trailing pad characters, if specified, are removed. If no trim specification (LEADING, TRAILING, or BOTH) is specified, then BOTH is assumed. If no trim_expression is used, a single space is assumed. If either the trim_expression or the source_expression evaluates to null, the result of the trim function is null. The maximum length of the resultant string must be 255 or less, because the VARCHAR data type supports only 255 characters.

Some generic uses for the TRIM function are shown in the following example:

SELECT TRIM (c1) FROM tab;
SELECT TRIM (TRAILING '#' FROM c1) FROM tab;
SELECT TRIM (LEADING FROM c1) FROM tab;
UPDATE c1='xyz' FROM tab WHERE LENGTH(TRIM(c1))=5;
SELECT c1, TRIM(LEADING '#' FROM TRIM(TRAILING '%' FROM

	'###abc%%%')) FROM tab;

When you use the DESCRIBE statement with a SELECT statement that uses the TRIM function in the select list, the described character type of the trimmed column depends on the database server you are using and the data type of the source_expression. For further information on the GLS aspects of the TRIM function in ESQL/C, see the Informix Guide to GLS Functionality.

The TRIM function can be specified on fixed-length character columns. If the length of the string is not completely filled, the unused characters are padded with blank space. Figure 4-3 shows this concept for the column entry '##A2T##', where the column is defined as CHAR(10).

Figure 4-3 Column Entry in a Fixed-Length Character Column

If you want to trim the pound sign (#) trim_expression from the column, you need to consider the blank padded spaces as well as the actual characters. For example, if you specify the trim specification BOTH, the result from the trim operation is A2T##, because the TRIM function does not match the blank padded space that follows the string. In this case, the only pound signs (#) trimmed are those that precede the other characters. The SELECT statement is shown, followed by Figure 4-4, which presents the result.

SELECT TRIM(LEADING '#' FROM col1) FROM taba

 






Figure 4-4  
Result of TRIM Operation






 

The following SELECT statement removes all occurrences of the pound sign (#):

SELECT TRIM(BOTH '#' FROM TRIM(TRAILING ' ' FROM col1)) FROM taba

The SUBSTRING function returns a subset of a source string.

You can use this function only with built-in data types.

The subset begins at the column position that start_position specifies. The following table shows how the database server determines the starting position of the returned subset based on the input value of the start_position.

The size of the subset is specified by length. The length parameter refers to the number of logical characters rather than to the number of bytes. If you omit the length parameter, the SUBSTRING function returns the entire portion of source_ string that begins at start_position.

In the following example, the user specifies that the subset of the source string that begins in column position 3 and is two characters long should be returned.

SELECT SUBSTRING('ABCDEFG' FROM 3 FOR 2)

	FROM mytable

The following table shows the output of this SELECT statement.

In the following example, the user specifies a negative start_position for the return subset.

SELECT SUBSTRING('ABCDEFG' FROM -3 FOR 7)

	FROM mytable

The database server starts at the -3 position (four positions before the first character) and counts forward for 7 characters. The following table shows the output of this SELECT statement.

The SUBSTR function has the same purpose as the SUBSTRING function (to return a subset of a source string), but it uses different syntax.

You can use this function only with built-in data types.

The SUBSTR function returns a subset of source_string. The subset begins at the column position that start_position specifies. The following table shows how the database server determines the starting position of the returned subset based on the input value of the start_position.

The length parameter specifies the number of characters (not bytes) in the subset. If you omit the length parameter, the SUBSTR function returns the entire portion of source_ string that begins at start_position.

In the following example, the user specifies that the subset of the source string to be returned begins at a starting position 3 characters back from the end of the string. Because the source string is 7 characters long, the starting position is the fifth column of source_string. Because the user does not specify a value for length, the database server returns the entire portion of the source string that begins in column position 5.

SELECT SUBSTR('ABCDEFG', -3)

	FROM mytable

The following table shows the output of this SELECT statement.

The REPLACE function replaces specified characters within a source string with different characters.

You can use this function only with built-in data types.

The REPLACE function returns a copy of source_string in which every occurrence of old_string is replaced by new_string. If you omit the new_string option, every occurrence of old_string is omitted from the return string.

In the following example, the user replaces every occurrence of xz in the source string with t.

SELECT REPLACE('Mighxzy xzime', 'xz', 't')

	FROM mytable

The following table shows the output of this SELECT statement.

The LPAD function returns a copy of source_string that is left-padded to the total number of characters specified by length.

You can use this function only with built-in data types.

The pad_string parameter specifies the pad character or characters to be used for padding the source string. The sequence of pad characters occurs as many times as necessary to make the return string reach the length specified by length. The sequence of pad characters in pad_string is truncated if it is too long to fit into length. If you omit the pad_string parameter, the default value is a single blank.

In the following example, the user specifies that the source string is to be left-padded to a total length of 16 characters. The user also specifies that the pad characters are a sequence consisting of a dash and an underscore (-_).

SELECT LPAD('Here we are', 16, '-_')

	FROM mytable

The following table shows the output of this SELECT statement.

The RPAD function returns a copy of source_string that is right-padded to the total number of characters that length specifies.

You can use this function only with built-in data types.

The pad_string parameter specifies the pad character or characters to be used to pad the source string. The sequence of pad characters occurs as many times as necessary to make the return string reach the length that length specifies. The sequence of pad characters in pad_string is truncated if it is too long to fit into length. If you omit the pad_string parameter, the default value is a single blank.

In the following example, the user specifies that the source string is to be right-padded to a total length of 18 characters. The user also specifies that the pad characters to be used are a sequence consisting of a question mark and an exclamation point (?!)

SELECT RPAD('Where are you', 18, '?!')

	FROM mytable

The following table shows the output of this SELECT statement.

The case-conversion functions enable you to perform case-insensitive searches in your queries and specify the format of the output. The case-conversion functions are UPPER, LOWER, and INITCAP. The following diagram shows the syntax of these case-conversion functions.

The input type of source_expression must be a character data type. When the column is described, the data type the database server returns is the same as the input type. For example, if the input type is CHAR, the output type is also CHAR.

You can use these functions only with built-in data types.

The byte length returned from the describe of a column with a case-conversion function is the input byte length of the source string. If you use a case-conversion function with a multibyte source_expression, the conversion might increase or decrease the length of the string. If the byte length of the result string exceeds the byte length of source_expression, the database server truncates the result string to fit into the byte length of source_expression.

If source_expression is null, the result of a case-conversion function is also null.

The database server treats a case-conversion function as an SPL routine in the following instances:

• If it has no argument
• If it has one argument, and that argument is a named argument
• If it has more than one argument
• If it appears in a SELECT list with a host variable as an argument

If none of the conditions in the preceding list are met, the database server treats a case-conversion function as a system function.

The following example shows how you can use all the case-conversion functions in the same query to specify multiple output formats for the same value:

Input value:



SAN Jose



Query:



SELECT City, LOWER(City), Lower("City"), 

	UPPER (City), INITCAP(City) 

		FROM Weather;



Query output: 



SAN Jose      san jose      city      SAN JOSE      San Jose

The UPPER function returns a copy of the source_expression in which every lowercase alphabetical character in the source_expression is replaced by a corresponding uppercase alphabetic character.

The following example shows how to use the UPPER function to perform a case-insensitive search on the lname column for all employees with the last name of curran:

SELECT title, INITCAP(fname), INITCAP(lname) FROM employees
WHERE UPPER (lname) = "CURRAN"

Because the INITCAP function is specified in the select list, the database server returns the results in a mixed-case format. For example, the output of one matching row might read: accountant James Curran.

The LOWER function returns a copy of the source_expression in which every uppercase alphabetic character in the source_expression is replaced by a corresponding lowercase alphabetic character.

The following example shows how to use the LOWER function to perform a case-insensitive search on the City column. This statement directs the database server to replace all instances (that is, any variation) of the words san jose, with the mixed-case format, San Jose.

UPDATE Weather SET City = "San Jose" 
WHERE LOWER (City) = "san jose";

The INITCAP function returns a copy of the source_expression in which every word in the source_expression begins with uppercase letter. With this function, a word begins after any character other than a letter. Thus, in addition to a blank space, symbols such as commas, periods, colons, and so on, introduce a new word.

For an example of the INITCAP function, see UPPER Function.

The IFX_ALLOW_NEWLINE function sets a newline mode that allows newline characters in quoted strings or disallows newline characters in quoted strings within a given session.

If you enter 't' as the argument of this function, you enable newline characters in quoted strings in the session. If you enter 'f' as the argument of this function, you disallow newline characters in quoted strings in the session.

You can set the newline mode for all sessions by setting the ALLOW_NEWLINE parameter in the ONCONFIG file to a value of 0 (newline characters not allowed) or to a value of 1 (newline characters allowed). If you do not set this configuration parameter, the default value is 0. Each time you start a session, the new session inherits the newline mode set in the ONCONFIG file. To change the newline mode for the session, execute the IFX_ALLOW_NEWLINE function. Once you have set the newline mode for a session, the mode remains in effect until the end of the session or until you execute the IFX_ALLOW_NEWLINE function again within the session.

In the following example, assume that you did not specify any value for the ALLOW_NEWLINE parameter in the ONCONFIG file, so by default newline characters are not allowed in quoted strings in any session. After you start a new session, you can enable newline characters in quoted strings in that session by executing the IFX_ALLOW_NEWLINE function:

EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('t')

The newline mode that is set by the ALLOW_NEWLINE parameter in the ONCONFIG file or by the execution of the IFX_ALLOW_NEWLINE function in a session applies only to quoted-string literals in SQL statements. The newline mode does not apply to quoted strings contained in host variables in SQL statements. Host variables can contain newline characters within string data regardless of the newline mode currently in effect. For example, you can use a host variable to insert data containing newline characters into a column even if the ALLOW_NEWLINE parameter in the ONCONFIG file is set to 0.

For further information on how the IFX_ALLOW_NEWLINE function affects quoted strings, see Quoted String. For further information on the ALLOW_NEWLINE parameter in the ONCONFIG file, see the Administrator's Reference.

A user-defined function is a function that you write in SPL or in a language external to the database, such as C or Java.

You can call user-defined functions within SQL statements. Unlike built-in functions, user-defined functions can only be used by the creator of the function, the DBA, and the users who have been granted the Execute privilege on the function. For more information, see GRANT.

The following examples show some user-defined function expressions. The first example omits the parameter option when it lists the function argument:

read_address('Miller')

This second example uses the parameter option to specify the argument value:

read_address(lastname = 'Miller')

When you use the parameter option, the parameter name must match the name of the corresponding parameter in the function registration. For example, the preceding example assumes that the read_address() function had been registered as follows:

     CREATE FUNCTION read_address(lastname CHAR(20))
     RETURNING address_t ...

A statement-local variable (SLV) enables you to transmit a value from a user-defined function call to another part of the SQL statement. To use an SLV with a call to a user-defined function, follow these steps:

• Write an OUT parameter for the user-defined function.

For more information on how to write a user-defined function with an OUT parameter, see Extending Informix Dynamic Server 2000.

• When you register the user-defined function, specify the OUT keyword in front of the OUT parameter.

For more information, see Specifying an OUT Parameter for a User-Defined Function.

• Declare the SLV in a function expression that calls the user-defined function with the OUT parameter.

The call to the user-defined function must be made within a WHERE clause. See Statement-Local Variable Declaration for information about the syntax to declare the SLV.

• Use the SLV that the user-defined function has initialized within the SQL statement.

Once the call to the user defined function has initialized the SLV, you can use this value in other parts of the SQL statement. See Statement-Local Variable Expressions for information about the use of an SLV within an SQL statement.

Statement-Local Variable Declaration

The Statement-Local Variable Declaration declares a statement-local variable (SLV) in a call to a user-defined function that defines an OUT parameter.

You declare an SLV in a user-defined function call so that a user-defined function can assign the value of its OUT parameter to the SLV. The call to the user-defined function must exist in the WHERE clause of the SQL statement. For example, if you register a function with the following CREATE FUNCTION statement, you can use its y parameter as a statement-local variable in a WHERE clause:

CREATE FUNCTION find_location(a FLOAT, b FLOAT, OUT y INTEGER)
RETURNING VARCHAR(20)
EXTERNAL NAME "/usr/lib/local/find.so"
LANGUAGE C

In this example, find_location() accepts two FLOAT values that represent a latitude and a longitude and returns the name of the nearest city, along with an extra value of type INTEGER that represents the population rank of the city.

You can now call find_location() in a WHERE clause:

SELECT zip_code_t FROM address
	WHERE address.city = find_location(32.1, 35.7, rank # INT)
	AND rank < 101;

The function expression passes two FLOAT values to find_location() and declares an SLV named rank of type INT. In this case, find_location() will return the name of the city nearest latitude 32.1 and longitude 35.7 (which may be a heavily populated area) whose population rank is between 1 and 100. The statement will then return the zip code that corresponds to that city.

The WHERE clause of the SQL statement must produce an SLV that is used within other parts of the statement. The following SELECT statement is illegal because the select list produces the SLV:

-- illegal SELECT statement
SELECT title, contains(body, 'dog and cat', rank # INT), rank
	FROM documents

The data type you use when you declare the SLV in a statement must be the same as the data type of the OUT parameter in the CREATE FUNCTION statement. If you use different but compatible data types, such as INTEGER and FLOAT, the database server automatically performs the cast between the data types.

SLVs shared the name space with UDR variables and the column names of the table involved in the SQL statement. Therefore, the database uses the following precedence to resolve ambiguous situations:

• UDR variables
• Column names
• SLVs

Once the user-defined function assigns its OUT parameter to the SLV, you can use this SLV value in other parts of the SQL statement. For more information, see Statement-Local Variable Expressions.

The Statement-Local Variable Expression specifies how you can use a defined statement-local variable (SLV) elsewhere in an SQL statement.

You define an SLV in the call to a user-defined function in the WHERE clause of the SQL statement. This user-defined function must be defined with an OUT parameter. The call to the user-defined function assigns the value of the OUT parameter to the SLV. For more information, see Statement-Local Variable Declaration.

Once the user-defined function assigns its OUT parameter to the SLV, you can use this value in other parts of the SQL statement. You can use the value of this OUT parameter elsewhere in the statement, subject to the following scoping rules:

• The SLV is read-only throughout the query (or subquery) in which it is defined.
• The scope of an SLV extends from the query in which the SLV is defined down into all nested subqueries.

In other words, if a query contains a subquery, an SLV that is visible in the query is also visible to all subqueries of that query.

• In nested queries, the scope of an SLV does not extend upwards.

In other words, if a query contains a subquery and the SLV is defined in the subquery, it is not visible to the parent query.

• In queries that involve UNION, the SLV is only visible in the query in which it is defined.

The SLV is not visible to all other queries involved in the UNION.

• For INSERT, DELETE, and UPDATE statements, an SLV is not visible outside the SELECT portion of the statement.

Within this SELECT portion, all the above scoping rules apply.

Important: A statement-local variable is valid only for the life of a single SQL statement.

The following SELECT statement calls the find_location() function in a WHERE clause and defines the rank SLV. In this example, find_location() accepts two values that represent a latitude and a longitude and returns the name of the nearest city, along with an extra value of type INTEGER that represents the population rank of the city.

SELECT zip_code_t FROM address
	WHERE address.city = find_location(32.1, 35.7, rank # INT)
	AND rank < 101;

When execution of the find_location() function completes successfully, the function has initialized the rank SLV. The SELECT then uses this rank value in a second WHERE-clause condition. In this example, the Statement-Local Variable Expression is the variable rank in the second WHERE-clause condition:

rank < 101

If the user-defined function that initializes the SLV is not executed in an iteration of the statement, the SLV has a value of null. SLV values do not persist across iterations of the statement. At the start of each iteration, the database server sets the SLV value to null.

Each user-defined function can have only one OUT parameter and one SLV. However, a single SQL statement can invoke multiple functions that have OUT parameters. For example, the following partial statement calls two user-defined functions with OUT parameters, whose values are referenced with the SLV names out1 and out2:

SELECT... 

	WHERE func_2(x, out1 # INTEGER) < 100 

	AND (out1 = 12 OR out1 = 13) 

	AND func_3(a, out2 # FLOAT) = "SAN FRANCISCO" 

	AND out2 = 3.1416;

For more information on how to write a user-defined function with an OUT parameter, see Extending Informix Dynamic Server 2000.

An aggregate expression uses an aggregate function to summarize selected database data.

You cannot use an aggregate expression in a condition that is part of a WHERE clause unless you use the aggregate expression within a subquery.

An aggregate function returns one value for a set of queried rows. The following examples show aggregate functions in SELECT statements:

SELECT SUM(total_price) FROM items WHERE order_num = 1013



SELECT COUNT(*) FROM orders WHERE order_num = 1001



SELECT MAX(LENGTH(fname) + LENGTH(lname)) FROM customer

If you use an aggregate function and one or more columns in the select list, you must put all the column names that are not used as part of an aggregate or time expression in the GROUP BY clause.

You can use two types of aggregate expressions in SQL statements: built-in aggregates and user-defined aggregates. The built-in aggregates include all the aggregates shown in the syntax diagram in Aggregate Expressions except for the "User-Defined Aggregates" category. User-defined aggregates are any new aggregates that the user creates with the CREATE AGGREGATE statement.

Built-in aggregates are aggregate expressions that are provided by the database server, such as AVG, SUM, and COUNT. By default, these aggregates work only with built-in data types, such as INTEGER and FLOAT.

You can extend these built-in aggregates to work with extended data types. To extend built-in aggregates, you must create UDRs that overload several binary operators.

Once you have overloaded the binary operators for a built-in aggregate, you can use that aggregate with an extended data type in an SQL statement. For example, if you have overloaded the plus operator for the SUM aggregate to work with a specified row type and assigned this row type to the complex column of the complex_tab table, you can apply the SUM aggregate to the complex column:

SELECT SUM(complex) FROM complex_tab

For further information on extending built-in aggregates, see the Extending Informix Dynamic Server 2000 manual.

For information on invoking built-in aggregates, see the descriptions of individual built-in aggregates in the following pages.

A user-defined aggregate is an aggregate that you define to perform an aggregate computation that is not provided by the database server. For example, you can create a user-defined aggregate named SUMSQ that returns the sum of the squared values of a specified column. User-defined aggregates can work with built-in data types or extended data types or both, depending on how you define the support functions for the user-defined aggregate.

To create a user-defined aggregate, use the CREATE AGGREGATE statement. In this statement you name the new aggregate and specify the support functions for the aggregate. Once you have created the new aggregate and its support functions, you can use the aggregate in SQL statements. For example, if you have created the SUMSQ aggregate and specified that it works with the FLOAT data type, you can apply the SUMSQ aggregate to a FLOAT column named digits in the test table:

SELECT SUMSQ(digits) FROM test

For further information on creating user-defined aggregates, see CREATE AGGREGATE and the discussion of user-defined aggregates in the Extending Informix Dynamic Server 2000 manual.

For information on invoking user-defined aggregates, see User-Defined Aggregates.

As indicated in the diagrams for Aggregate Expressions and User-Defined Aggregates, not all expressions are available for you to use when you use an aggregate expression. The argument of an aggregate function cannot itself contain an aggregate function. You cannot use the aggregate functions in the following situations:

• MAX(AVG(order_num))
• An aggregate function in a WHERE clause, unless it is contained in a subquery, or if the aggregate is on a correlated column that originates from a parent query and the WHERE clause is within a subquery that is within a HAVING clause
• An aggregate function on a BYTE or TEXT column

You cannot use a collection column as an argument to the following aggregate functions:

• AVG
• SUM
• MIN
• MAX

For the full syntax of expressions, see Expression.

The DISTINCT keyword causes the function to be applied only to unique values from the named column. The UNIQUE keyword is a synonym for the DISTINCT keyword.

The ALL keyword is the opposite of the DISTINCT keyword. If you specify the ALL keyword, all the values that are selected from the named column or expression, including any duplicate values, are used in the calculation.

The COUNT function is actually a set of functions that enable you to count column values and expressions in different ways. You invoke each form of the COUNT function by specifying a particular argument after the COUNT keyword. Each form of the COUNT function is explained in the following subsections. For a comparison of the different forms of the COUNT function, see Comparison of the Different COUNT Functions.

The COUNT (*) function returns the number of rows that satisfy the WHERE clause of a SELECT statement. The following example finds how many rows in the stock table have the value HRO in the manu_code column:

SELECT COUNT(*) FROM stock WHERE manu_code = 'HRO'

If the SELECT statement does not have a WHERE clause, the COUNT (*) function returns the total number of rows in the table. The following example finds how many rows are in the stock table:

SELECT COUNT(*) FROM stock

If the SELECT statement contains a GROUP BY clause, the COUNT (*) function reflects the number of values in each group. The following example is grouped by the first name; the rows are selected if the database server finds more than one occurrence of the same name:

SELECT fname, COUNT(*) FROM customer

	GROUP BY fname

	HAVING COUNT(*) > 1

If the value of one or more rows is null, the COUNT (*) function includes the null columns in the count unless the WHERE clause explicitly omits them.

The COUNT DISTINCT function return the number of unique values in the column or expression, as the following example shows. If the COUNT DISTINCT function encounters nulls, it ignores them.

SELECT COUNT (DISTINCT item_num) FROM items

Nulls are ignored unless every value in the specified column is null. If every column value is null, the COUNT DISTINCT function returns a zero (0) for that column.

The UNIQUE keyword has exactly the same meaning as the DISTINCT keyword when the UNIQUE keyword is used with the COUNT keyword. The UNIQUE keyword returns the number of unique non-null values in the column or expression.

The following example uses the COUNT UNIQUE function, but it is equivalent to the preceding example that uses the COUNT DISTINCT function:

SELECT COUNT (UNIQUE item_num) FROM items