SQL Statements
Expression
An expression is one or more pieces of data that is contained in or derived from the database or database server. Use the Expression segment whenever you see a reference to an expression in a syntax diagram.
Syntax
Usage
An expression comprises many basic items. Each item is described in the following list.
The following sections describe the syntax of each of these expression items. You can also use SPL variables or host variables in an expression.
Concatenating Expressions
You can use the concatenation operator (||) to concatenate two expressions. 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.
You cannot use the concatenation operator in an embedded-language-only statement. The SQL API-only statements appear in the following list.
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 the Extending INFORMIX-Universal Server: Data Types manual.
Cast Expressions
|
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 has been defined to perform the conversion between two data types, the statement returns an error.
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.
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.
Column Expressions
The possible syntax for column expressions is shown in the following diagram.
The following examples show column expressions:
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.
Using Dot Notation
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:
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:
This use of dot notation is called a field projection. For example, suppose you have a column called rect with the following definition:
The following SELECT statement accesses field length of the rect column:
If the row definitions are nested, you can specify multiple levels of field names. For example, consider the following two tables:
The following SELECT statement references field d of row column c in table b.
When the meaning of a particular identifier is ambiguous, Universal Server uses the following precedence rules to determine which database object the identifier specifies in a dot notation of name1.name2.name3.name4:
1. schema name1.table name2.column name3.field name4
2. table name1.column name2.field name3.field name4
3. column name1.field name2.field name3.field name4
For more information about precedence rules and how to use dot notation with row columns, see Chapter 12 of the Informix Guide to SQL: Tutorial.
Using Subscripts on Character Columns
You can use subscripts on CHAR, VARCHAR, 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:
Column subscripting also works on NCHAR and NVARCHAR columns. For information on the GLS aspects of column subscripts and substrings, see the Guide to GLS Functionality.
Using Rowids
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 utilize 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:
In Universal Server only, 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 ROWID keyword in the select list of a query that contains an aggregate function.
Using Smart Large Objects
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:
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.
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):
For more information, see the INFORMIX-ESQL/C Programmer's Manual.
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:
If two columns share the same smart-large-object value, the data might be locked by either column that needs to access it.
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 SQL 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 Chapter 2 of the Informix Guide to SQL: Reference.
Constant Expressions
The following diagram shows the possible syntax for constant expressions.
The following examples show constant expressions:
The following list provides references for further information:
- For quoted strings as expressions, see "Quoted String as an Expression".
- For the USER function in an expression, see "USER Function".
- For the SITENAME and DBSERVERNAME functions in an expression, refer to "SITENAME and DBSERVERNAME Functions".
- For literal numbers as expressions, see "Literal Number as an Expression".
- For the TODAY function in an expression, see "TODAY Function".
- For the CURRENT function in an expression, see "CURRENT Function".
- For literal DATETIME as an expression, see "Literal DATETIME as an Expression".
- For literal INTERVAL as an expression, see "Literal INTERVAL as an Expression".
- For the UNITS keyword in an expression, see "The UNITS Keyword".
- For literal collections as expressions, see "Literal Collection as an Expression".
Quoted String as an Expression
The following examples show quoted strings as expressions:
USER Function
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:
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 be CHAR, VARCHAR, NCHAR, or NVARCHAR data type, and it must be at least eight characters long.
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.
SITENAME and DBSERVERNAME Functions
The SITENAME and DBSERVERNAME functions return the database server name, as defined in the ONCONFIG file for the Universal Server installation where the current database resides or as specified in the INFORMIXSERVER environment variable. The two function names, SITENAME and DBSERVERNAME, 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 be CHAR, VARCHAR, NCHAR, or NVARCHAR data type and must be at least 18 characters long.
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.
Literal Number as an Expression
The following examples show literal numbers as expressions:
TODAY Function
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:
CURRENT Function
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 qualifiers are YEAR TO FRACTION(3). You can use the CURRENT function in any context in which you can use a literal DATETIME (see page 1-995). 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:
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 calls to CURRENT from an EXECUTE PROCEDURE statement return the value when the stored procedure 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 1993 to the current instant.
Literal DATETIME as an Expression
The following examples show literal DATETIME as an expression:
Literal INTERVAL as an Expression
The following examples show literal INTERVAL as an expression:
The second statement in the preceding example adds five days to each value of lead_time selected from the manufact table.
The UNITS Keyword
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.
Literal Collection as an Expression
The following examples show literal collections as expressions:
For more information, see "Literal DATETIME".
Literal Row as an Expression
The following examples show literal collections 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".
Constructor Expressions
A constructor is a function that the database server uses to create an instance of a particular data type. Universal Server supports a ROW constructor. The syntax for expressions that use a ROW constructor is shown in the following diagram.
You can use any kind of expression with a ROW constructor, including literals, functions, and variables. The following examples show row expressions:
Using ROW Constructors
Suppose you create the following named row type and a table that contains the named row type row_t and an unnamed row type:
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 do the following:
The format of the value for each field must be compatible with the data type of the row field to which it is assigned.
The following statement uses ROW constructors to insert values into col1 and col2 of the new_tab table:
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:
Use a ROW constructor anytime you want to generate a row type value. In the following example, a ROW constructor specifies a row type value that is cast as type person_t:
See the INSERT, UPDATE, and SELECT statements in this manual. See the CREATE ROW TYPE statement for information on named row types.
In the Informix Guide to SQL: Reference, see the ROW data type in Chapter 2 for information on unnamed row types. In the Informix Guide to SQL: Tutorial, see Chapter 10 for information on named row types and unnamed row types.
Function Expressions
A function expression can call built-in functions or user-defined functions, as the following diagram shows.
The following examples show function expressions:
Algebraic Functions
An algebraic function takes one or more arguments, as the following diagram shows.
ABS() Function
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 stores7 database does not contain any negative balances; however, you might have negative balances in your application.
MOD() Function
The MOD() function returns the modulus or remainder value for two numeric expressions. You provide integer expressions for the dividend and divisor. The divisor cannot be 0.
In earlier Informix products, the MOD() function returned an INT value. However, in Universal Server, the MOD() function returns an INT8 value.
The following example uses a 30-day billing cycle to determine how far today is into the billing cycle:
POW() Function
The POW() function raises the base to the exponent. This function requires two numeric arguments. The return type is FLOAT. The following example returns all the information for circles whose areas ( r) are less than 1,000 square units:
ROOT() Function
The ROOT() function returns the root value of a numeric expression. This function requires at least one numeric argument (the radicand argument) and allows no more than two (the radicand and index arguments). If only the radicand argument is supplied, the value 2 is used as a default value for the index argument. The value 0 cannot be used as the value of index. The value that the ROOT() function returns is FLOAT. The first SELECT statement in the following example takes the square root of the expression. The second SELECT statement takes the cube root of the expression.
The SQRT() function uses the form SQRT(x)=ROOT(x) if no index is given.
ROUND() Function
The ROUND() function returns the rounded value of an expression. The expression must be numeric or must be converted to numeric.
If you omit the digit indication, the value is rounded to zero digits or to the ones place. The digit limitation of 32 (+ and -) refers to the entire decimal value.
Positive-digit values indicate rounding to the right of the decimal point; negative-digit values indicate rounding to the left of the decimal point, as Figure 1-3 shows.
The following example shows how you can use the ROUND() function with a column expression in a SELECT statement. This statement displays the order number and rounded total price (to zero places) of items whose rounded total price (to zero places) is equal to 124.00.
If you use a MONEY data type as the argument for the ROUND() function and you round to zero places, the value displays with .00. The SELECT statement in the following example rounds an INTEGER value and a MONEY value. It displays 125 and a rounded price in the form xxx.00 for each row in items.
SQRT() Function
The SQRT() function returns the square root of a numeric expression.
The following example returns the square root of 9 for each row of the angles table:
TRUNC() Function
The TRUNC() function returns the truncated value of a numeric expression.
The expression must be numeric or a form that can be converted to a numeric expression. If you omit the digit indication, the value is truncated to zero digits or to the one's place. The digit limitation of 32 (+ and -) refers to the entire decimal value.
Positive digit values indicate truncating to the right of the decimal point; negative digit values indicate truncating to the left of the decimal point, as Figure 1-4 shows.
If you use a MONEY data type as the argument for the TRUNC() function and you truncate to zero places, the .00 places are removed. For example, the following SELECT statement truncates a MONEY value and an INTEGER value. It displays 125 and a truncated price in integer format for each row in items.
CARDINALITY() Function
The CARDINALITY() function returns the number of elements in a collection column (SET, MULTISET, LIST). Suppose that the set_col SET column contains the following value:
The following SELECT statement returns 5 as the number of elements in the set_col column:
If the collection contains duplicate elements, CARDINALITY() counts each individual element.
DBINFO() Function
Use the DBINFO() function for any of the following purposes:
You can use the DBINFO() function anywhere within SQL statements and within routines.
Using the 'DBSPACE' Option
The 'DBSPACE' option returns a character string that contains the name of the dbspace corresponding to a tblspace number. You must supply an additional parameter, either tblspace num or an expression that evaluates to tblspace num. The following example uses the 'DBSPACE' option. First, it queries the systables system catalog table to determine the tblspace num for the table customer, then it executes the function to determine the dbspace name.
If the statement returns a partition number of 16777289, you insert that value into the second argument to find which dbspace contains the customer table, as shown in the following example:
Using the 'sqlca.sqlerrd1' Option
The 'sqlca.sqlerrd1' option returns a single integer that provides the last SERIAL value that is inserted into a table. To ensure valid results, use this option immediately following an INSERT statement that inserts a SERIAL value.
The following example uses the 'sqlca.sqlerrd1' option:
This example inserts a row that contains a primary-key SERIAL value into the fst_tab table, and then uses the DBINFO() function to insert the same SERIAL value into the sec_tab table. The value that the DBINFO() function returns is the SERIAL value of the last row that is inserted into fst_tab.
Using the 'sqlca.sqlerrd2' Option
The 'sqlca.sqlerrd2' option returns a single integer that provides the number of rows that SELECT, INSERT, DELETE, UPDATE, EXECUTE FUNCTION and EXECUTE PROCEDURE statements processed. To ensure valid results, use this option after SELECT and EXECUTE PROCEDURE statements have completed executing. In addition, if you use this option within cursors, make sure that all rows are fetched before the cursors are closed to ensure valid results.
The following example shows a stored procedure that uses the 'sqlca.sqlerrd2' option to determine the number of rows that are deleted from a table:
Using the 'sessionid' Option
The 'sessionid' option of the DBINFO() function returns the session ID of your current session.
When a client application makes a connection to Universal Server, the database server starts a session with the client and assigns a session ID for the client. The session ID serves as a unique identifier for a given connection between a client and a database server. The database server stores the value of the session ID in a data structure in shared memory that is called the session control block. The session control block for a given session also includes the user ID, the process ID of the client, the name of the host computer, and a variety of status flags.
When you specify the 'sessionid' option, the database server retrieves the session ID of your current session from the session control block and returns this value to you as an integer. Some of the System-Monitoring Interface (SMI) tables in the sysmaster database include a column for session IDs, so you can use the session ID that the DBINFO() function obtained to extract information about your own session from these SMI tables. For further information on the session control block, the sysmaster database, and the SMI tables, see the INFORMIX-Universal Server Administrator's Guide.
In the following example, the user specifies the DBINFO() function in a SELECT statement to obtain the value of the current session ID. The user poses this query against the systables system catalog table and uses a WHERE clause to limit the query result to a single row.
The following table shows the result of this query.
In the preceding example, the SELECT statement queries against the systables system catalog table. However, you can obtain the session ID of the current session by querying against any system catalog table or user table in the database. For example, you can enter the following query to obtain the session ID of your current session:
The following table shows the result of this query.
You can use the DBINFO() function not only in SQL statements but also in stored procedures. The following example shows a stored procedure that returns the value of the current session ID to the calling program or procedure:
Using the 'serial8' Option
The 'serial8' option returns a single integer that provides the last SERIAL8 value that is inserted into a table. To ensure valid results, use this option immediately following an INSERT statement that inserts a SERIAL8 value.
The following example uses the 'serial8' option:
This example inserts a row that contains a primary-key SERIAL8 value into the fst_tab table, and then uses the DBINFO() function to insert the same SERIAL8 value into the sec_tab table. The value that the DBINFO() function returns is the SERIAL8 value of the last row that is inserted into fst_tab. The subquery in the last line contains a WHERE clause so that a single value is returned.
Exponential and Logarithmic Functions
Exponential and logarithmic functions take at least one argument. The return type is FLOAT. The following example shows exponential and logarithmic functions.
EXP() Function
The EXP() function returns the exponential value of two numeric expressions. You provide a constant and float expression in the form e(n)=en. The following example returns the exponent of 3 for each row of the angles table:
LOGN() Function
The LOGN() function returns the natural log of a numeric expression. The logarithmic value is the inverse of the exponential value. The following SELECT statement returns the natural log of population for each row of the history table:
LOG10() Function
The LOG10() function returns the log of a value to the base 10. The following example returns the log base 10 of distance for each row of the travel table:
HEX() Function
The HEX() function returns the hexadecimal encoding of an integer expression. The following example displays the data type and column length of the columns of the orders table in hexadecimal format. For MONEY and DECIMAL columns, you can then determine the precision and scale from the lowest and next-to-the-lowest bytes. For VARCHAR and NVARCHAR columns, you can determine the minimum space and maximum space from the lowest and next to the lowest bytes. (See Chapter 1 of the Informix Guide to SQL: Reference for more information about encoded information.)
The following example lists the names of all the tables in the current database and their corresponding tblspace number in hexadecimal format. This example is particularly useful because the two most significant bytes in the hexadecimal number constitute the dbspace number. They are used to identify the table in oncheck output.
The HEX() function can operate on an expression, as the following example shows:
Length Functions
You can use length functions to determine the length of a column, string, or variable. The length functions are LENGTH(), OCTET_LENGTH(), and CHAR_LENGTH(). Each of these functions has a distinct purpose.
LENGTH() Function
The LENGTH() function returns the number of bytes in a character column, not including any trailing spaces. With TEXT or BYTE columns, the LENGTH() function returns the full number of bytes in the column, including trailing spaces.
The following example illustrates the use of the LENGTH() function:
You can use the LENGTH() function to return the length of a character variable. 
For information on GLS aspects of the LENGTH function, see the Guide to GLS Functionality.
OCTET_LENGTH() Function
The OCTET_LENGTH() function returns the number of bytes in a character column, including any trailing spaces. See the Guide to GLS Functionality for a discussion of the OCTET_LENGTH() function.
CHAR_LENGTH() Function
The CHAR_LENGTH() function returns the number of characters (not bytes) in a character column. See the Guide to GLS Functionality for a discussion of the CHAR_LENGTH() function.
Shared-Library Functions
To execute an external routine, the database server loads the shared library that contains this function into shared memory. A shared-library function allows you to update the copy of a shared library that the database server has loaded into memory.
IFX_RELOAD_MODULE Function
The IFX_RELOAD_MODULE function reloads a loaded shared library into the shared memory for the database server. This function replaces the existing version with a new version that has the same name and location. The function returns an integer value to indicate the status of the update, as follows:
For example, to reload a new version of the circle.so shared library that resides in the /usr/app/opaque_types directory, you can use the EXECUTE FUNCTION statement to execute the IFX_RELOAD_MODULE function, as follows:
To execute the IFX_RELOAD_MODULE function in an INFORMIX-ESQL/C application, you must associate the function with a cursor.
For more information on how to use IFX_RELOAD_MODULE to update a shared library, see the chapter on how to design a user-defined routine in the Extending INFORMIX-Universal Server: User-Defined Routines manual.
IFX_REPLACE_MODULE Function
The IFX_REPLACE_MODULE function replaces a loaded shared library with a new version that has a different name or location. The function returns an integer value to indicate the status of the update, as follows:
For example, to replace the circle.so shared library that resides in the /usr/app/opaque_types directory with one that resides in the /usr/app/shared_libs directory, you can use the following EXECUTE FUNCTION statement to execute the IFX_REPLACE_MODULE:
To execute the IFX_REPLACE_MODULE function in an INFORMIX-ESQL/C appliation, you must associate the function with a cursor.
For more information on how to use IFX_REPLACE_MODULE to update a shared library, see the chapter on how to design a user-defined routine in the Extending INFORMIX-Universal Server: User-Defined Routines manual.
Smart-Large-Object Functions
FILETOBLOB and FILETOCLOB Functions
The FILETOBLOB function creates a BLOB value for data that is stored in a specified operating-system file. Similarly, the FILETOCLOB function creates a CLOB value for data that is stored in an operating-system file. These functions determine the operating-system file to use from the following parameters:
The table name and column name parameters are optional:
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 INFORMIX-Universal Server Administrator's Guide.
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 store 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:
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.
LOTOFILE Function
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:
By default, the LOTOFILE function generates a filename of the form:
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 must smart large objects would have an identifier with significantly fewer digits.
For example, suppose you specify a pathname value as follows:
If the CLOB column has an identifier of 203b2, the LOTOFILE function would create the file:
To change this default filename, you can specify the following wildcards in the filename of the pathname:
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:
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:
If you specify more than 17 question marks, the LOTOFILE function ignores them.
For example, suppose you specify a pathname value as follows:
The LOTOFILE function does not use the smart-large-object identifier in the filename so it generates the following file:
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.
LOCOPY Function
The LOCOPY function creates a copy of a smart large object. The first parameter specifies the BLOB or CLOB column to copy. The table name and column name parameters are optional:
It obtains the system-specific storage defaults from either the ONCONFIG file or the sbspace. For more information on system-specified storage defaults, see the INFORMIX-Universal Server Administrator's Guide.
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:
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.
Time Functions
DATE() Function
The DATE() function returns a DATE type 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:
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:
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 (1995), 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 '95/02/01'. For information on the format of DBDATE, see Chapter 3 of 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:
The database server searches for rows with an order_date value less than December 31, 1900 (12/31/1899 plus 365 days).
DAY() Function
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:
MONTH() Function
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:
WEEKDAY() Function
The WEEKDAY() function returns an integer that represents the day of the week; zero 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:
YEAR() Function
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:
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.
EXTEND() Function
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/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.
MDY() Function
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:
Trigonometric Functions
A trigonometric function takes an argument, as the following diagram shows.
Formulas for Radian Expressions
The COS(), SIN(), and TAN() functions take the number of radians (radian expression) as an argument.
If you are using degrees and want to convert degrees to radians, use the following formula:
If you are using radians and want to convert radians to degrees, use the following formula:
COS() Function
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.
SIN() Function
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:
TAN() Function
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:
ACOS() Function
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:
ASIN() Function
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:
ATAN() Function
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:
ATAN2() Function
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):
You can determine the length of the radial coordinate r using the expression shown in the following example:
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)
TRIM() Function
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 character value expression is used, a single space is assumed. If either the trim character value expression or the source character value 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:
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 character value expression. See the Guide to GLS Functionality for further information on the GLS aspects of the TRIM() function in ESQL/C.
Fixed Character Columns
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 1-5 shows this concept for the column entry '##A2T##', where the column is defined as CHAR(10).
If you want to trim the trim character value 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 '#' trimmed are those that precede the other characters. The SELECT statement is shown, followed by Figure 1-6, which presents the result.
The following SELECT statement removes all occurrences of '#':
User-Defined Functions
A user-defined function is a function that you write in SPL or in a language external to the database, such as the C language. User-defined functions contrast with functions that are built in to the database server. Unlike built-in functions, user-defined functions can only be used by the creator of the function, the DBA, and users who have been granted the Execute privilege on the function.
The database server identifies a function by the function name, the number of arguments the function accepts, the data type of each argument, and the order in which the arguments are listed. Because Universal Server allows function overloading, you can define more than one function of the same name, provided that the parameters differ in data type or order.
The following examples show some user-defined function expressions. The first example omits the parameter name option, and the second example uses the parameter name option:
If the function has an OUT parameter defined with the CREATE FUNCTION statement, you can declare a Statement Local Variable in the function expression, as described in the following sections.
Statement Local Variable Declaration
The Statement Local Variable Declaration declares a Statement Local Variable in a function expression in an SQL statement. You can then use the value the function returns through the Statement Local Variable elsewhere in the statement, as described in the section "Using Statement Local Variables."
Statement Local Variable Expression
Using Statement Local Variables
A Statement Local Variable transmits a value from a function call in a statement to another part of the SQL statement. You can use a Statement Local Variable in any WHERE clause.
To use a Statement Local Variable with a call to a user-defined function, you must take three steps:
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:
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 INT that represents the population rank of the city.
You can now call find_location in a WHERE clause:
The function expression passes two FLOAT values to find_location and declares a Statement Local Variable 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.
In the example, rank # INT is the Statement Local Variable Declaration and rank < 101 is the Statement Local Variable Expression.
The data type you use when you declare the Statement Local Variable 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.
Each function can have only one OUT parameter and one Statement Local Variable. However, you can use more than one Statement Local Variable in a WHERE clause, if they are produced by different functions.
For more information on OUT parameters and Statement Local Variables, see the Extending INFORMIX-Universal Server: User-Defined Routines manual.
Aggregate Expressions
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 the aggregate expression is used within a subquery.
The following diagram shows the syntax of aggregate function expressions.
An aggregate function returns one value for a set of queried rows. The following examples show aggregate functions in SELECT statements:
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.
Subset of Expressions Allowed in an Aggregate Expression
The argument of an aggregate function cannot itself contain an aggregate function. You cannot use the aggregate functions found in the following list:
You cannot use a collection column as an argument to the following aggregate functions:
For the full syntax of expressions, see page 1-880.
Including or Excluding Duplicates in the Row Set
The DISTINCT keyword causes the function to be applied to only 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.
COUNT Functions
You can use the different forms of the COUNT function to retrieve different types of information about a table. The following table summarizes the meaning of each form of the COUNT function.
Some examples can help to show the differences among the different forms of the COUNT function. The following examples pose queries against the orders table in the demonstration database. Most of the examples query against the ship_instruct column in this table. For information on the structure of the orders table and the data in the ship_instruct column, see the description of the demonstration database in the Informix Guide to SQL: Reference.
COUNT(*) Function
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:
If the SELECT statement does not have a WHERE clause, the COUNT (*) keyword returns the total number of rows in the table. The following example finds how many rows are in the stock table:
If the SELECT statement contains a GROUP BY clause, the COUNT(*) keyword 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:
If the value of one or more rows is null, the COUNT(*) keyword includes the null columns in the count unless the WHERE clause explicitly omits them.
In the following example, the user wants to know the total number of rows in the orders table. So the user uses the COUNT(*) function in a SELECT statement without a WHERE clause.
The following table shows the result of this query.
In the following example, the user wants to know how many rows in the orders table have a null value in the ship_instruct column. So the user uses the COUNT(*) function in a SELECT statement with a WHERE clause, and specifies the IS NULL condition in the WHERE clause.
The following table shows the result of this query.
In the following example, the user wants to know how many rows in the orders table have the value express in the ship_instruct column. So the user specifies the COUNT (*) function in the select list and the equals (=) relational operator in the WHERE clause.
The following table shows the result of this query.
COUNT DISTINCT and UNIQUE Keywords
The COUNT DISTINCT keywords return the number of unique values in the column or expression, as the following example shows. If the COUNT function encounters nulls, it ignores them.
Nulls are ignored unless every value in the specified column is null. If every column value is null, the COUNT keyword returns a zero for that column.
The UNIQUE keyword has exactly the same meaning as the DISTINCT keyword when the UNIQUE keyword is used within the COUNT function. The UNIQUE keyword returns the number of unique non-null values in the column or expression.
The following example uses the UNIQUE keyword, but it is equivalent to the preceding example that uses the DISTINCT keyword:
In the following example, the user wants to know how many unique non-null values are in the ship_instruct column of the orders table. So the user enters the COUNT DISTINCT function in the select list of the SELECT statement.
The following table shows the result of this query.
COUNT column name Option
The COUNT column name option returns the total number of non-null values in the column or expression, as the following example shows:
You can include the ALL keyword before the specified column name for clarity, but the query result is the same whether you include the ALL keyword or omit it.
The following example shows how to include the ALL keyword in the COUNT column name option:
In the following example the user wants to know how many non-null values are in the ship_instruct column of the orders table. So the user enters the COUNT column name function in the select list of the SELECT statement.
The following table shows the result of this query.
The user can also find out how many non-null values are in the ship_instruct column by including the ALL keyword in the parentheses that follow the COUNT keyword.
The following table shows that the query result is the same whether you include or omit the ALL keyword.
AVG() Function
The AVG() function returns the average of all values in the specified column or expression. You can apply the AVG() function only to number columns. If you use the DISTINCT keyword, the average (mean) is greater than only the distinct values in the specified column or expression. The query in the following example finds the average price of a helmet:
Nulls are ignored unless every value in the specified column is null. If every column value is null, the AVG function returns a null for that column.
MAX() Function
The MAX() function returns the largest value in the specified column or expression. Using the DISTINCT keyword does not change the results. The query in the following example finds the most expensive item that is in stock but has not been ordered:
Nulls are ignored unless every value in the specified column is null. If every column value is null, the MAX function returns a null for that column.
MIN() Function
The MIN() function returns the lowest value in the column or expression. Using the DISTINCT keyword does not change the results. The following example finds the least expensive item in the stock table:
Nulls are ignored unless every value in the specified column is null. If every column value is null, the MIN function returns a null for that column.
SUM() Function
The SUM() function returns the sum of all the values in the specified column or expression, as shown in the following example. If you use the DISTINCT keyword, the sum is for only distinct values in the column or expression.
Nulls are ignored unless every value in the specified column is null. If every column value is null, the SUM() function returns a null for that column.
You cannot use the SUM() function with a character column.
RANGE() Function
The RANGE() function computes the range for a sample of a population. It computes the difference between the maximum and the minimum values, as follows:
You can apply the RANGE() function only to numeric columns. The following query finds the range of ages for a population:
As with other aggregates, the RANGE() function applies to the rows of a group when the query includes a GROUP BY clause, as shown in the following example:
Nulls are ignored unless every value in the specified column is null. If every column value is null, the RANGE() function returns a null for that column.
STDEV() Function
The STDEV() function computes the standard deviation for a sample of a population. It is the square root of the VARIANCE() function.
You can apply the STDEV() function only to numeric columns. The following query finds the standard deviation on a population:
As with the other aggregates, the STDEV function applies to the rows of a group when the query includes a GROUP BY clause, as shown in the following example:
Nulls are ignored unless every value in the specified column is null. If every column value is null, the STDEV() function returns a null for that column.
VARIANCE() Function
The VARIANCE() keyword returns the variance for a sample of values as an unbiased estimate of the variance of the population. It computes the following value:
In this example, Xi is each value in the column and N is the total number of values in the column. You can apply the VARIANCE() function only to numeric columns. The following query finds the variance on a population:
As with the other aggregates, the VARIANCE() function applies to the rows of a group when the query includes a GROUP BY clause, as shown in the following example:
Nulls are ignored unless every value in the specified column is null. If every column value is null, the VARIANCE() function returns a null for that column.
Summary of Aggregate Function Behavior
An example can help to summarize the behavior of the aggregate functions. Assume that the testtable table has a single INTEGER column that is named a_number. The contents of this table are as follows.
You can use aggregate functions to obtain different types of information about the a_number column and the testtable table. In the following example, the user specifies the AVG function to obtain the average of all the non-null values in the a_number column:
The following table shows the result of this query.
You can use the other aggregate functions in SELECT statements that are similar to the one shown in the preceding example. If you enter a series of SELECT statements that have different aggregate functions in the select list and do not have a WHERE clause, you receive the results that the following table shows.
Error Checking with Aggregate Functions
Aggregate functions always return one row; if no rows are selected, the function returns a null. You can use the COUNT (*) keyword to determine whether any rows were selected, and you can use an indicator variable to determine whether any selected rows were empty. Fetching a row with a cursor associated with an aggregate function always returns one row; hence, 100 for end of data is never returned into the SQLCODE variable for a first fetch attempt.
You can also use the GET DIAGNOSTICS statement for error checking. See the GET DIAGNOSTICS statement in this manual.
Using Arithmetic Operators with Expressions
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:
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.
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 the Extending INFORMIX-Universal Server: Data Types manual.
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 the Extending INFORMIX-Universal Server: Data Types manual.
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:
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.
References
In the Informix Guide to SQL: Tutorial, see Chapter 2 for a discussion of expressions in the SELECT statement.
In the Guide to GLS Functionality, see the discussions of column expressions, the discussion of length functions, and the discussion of the TRIM() function.
|
