Syntax

Usage

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

lname[1,3] || zipcode 



:file_variable || '.dbg'



'Date:' || TODAY  

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

Element	Purpose	Restrictions	Syntax
target data type	The data type that results after the cast is applied.	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 type cannot be an unnamed row type or collection data type. An explicit or implicit cast must exist that can convert the data type of the expression to the target data type.	Data type, p. 1-859

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.

CAST (x + y) AS user_type

(x + y)::user_type

CAST expr AS INTEGER

expr::INTEGER

The following examples show column expressions:

company 



items.price 



cat_advert [1,15]

SELECT * FROM customer, orders 

	WHERE customer.customer_num = orders.customer_num



SELECT * FROM customer c, orders o

	WHERE c.customer_num = o.customer_num

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

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

row_name.field_name

CREATE TABLE rectangles

(

	area float,

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

)

SELECT rect.length FROM rectangles

WHERE area = 64

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

SELECT * 
FROM tab_b,tab_c 
WHERE tab_b.c.d = 10

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

Using Subscripts on Character Columns

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

lname[6,9]

Using Rowids

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

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

Using Smart Large Objects

• 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

Constant Expressions

Element	Purpose	Restrictions	Syntax
datetime unit	One of the units that is used to specify an interval precision; that is, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, or FRACTION. If the unit is YEAR, the expression is a year-month interval; otherwise, the expression is a day-time interval.	The datetime unit must be one of the keywords that is listed in the Purpose column. You can enter the keyword in uppercase or lowercase letters. You cannot put quotation marks around the keyword.	See the Restrictions column.
n	A literal number that you use to specify the number of datetime units. See "The UNITS Keyword" for more information on this parameter.	If n is not an integer, it is rounded down to the nearest whole number when it is used. The value that you specify for n must be appropriate for the datetime unit that you choose.	Literal Number, p. 1-1001,
literal opaque type	The literal representation for an opaque data type	The literal must be recognized by the input support function of the associated opaque type.	Defined by the developer of the opaque type.
literal BOOLEAN	The literal representation of a BOOLEAN value	A literal BOOLEAN can be only 't' (TRUE) or 'f'(FALSE).	Quoted string, p. 1-1014.

The following examples show constant expressions:

DBSERVERNAME 



TODAY 



'His first name is'



CURRENT YEAR TO DAY 



INTERVAL (4 10:05) DAY TO MINUTE



DATETIME (4 10:05) DAY TO MINUTE



5 UNITS YEAR



{2, 3, 4}

• 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".

SELECT 'The first name is ', fname FROM customer



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



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

	WHERE customer_num = 120 AND call_code = 'B'

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

ANSI

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

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.

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

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

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

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:

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

	DEFAULT CURRENT YEAR TO DAY)

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.

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 '1993-1-1 00:00:00' AND CURRENT

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



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

			YEAR TO MINUTE

	WHERE customer_num = 110

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



SELECT * FROM cust_calls 

	WHERE call_dtime

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

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

	INTERVAL (16) DAY TO DAY)



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

The UNITS Keyword

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'

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)

Literal Row as an Expression

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)

Constructor Expressions

You can use any kind of expression with a ROW constructor, including literals, functions, and variables. The following examples show row expressions:

ROW(5, 6.77, 'HMO')


ROW(col1.lname, 45000)


ROW('john davis', TODAY)


ROW(USER, SITENAME)

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

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

The following statement uses 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)
)

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

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

In the Informix Guide to SQL: Syntax, 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

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 (4,3)



MOD (10,3)

(1 of 3)

Element	Purpose	Restrictions	Syntax
base	A value to be raised to the power that is specified in exponent. The base value is the first argument that is supplied to the POW() function. See "POW() Function" for further information on base.	You can enter in base any real number or any expression that evaluates to a real number.	Expression, p. 1-880
dividend	A value to be divided by the value in divisor. The dividend value is the first argument supplied to the MOD() function. See "MOD() Function" for further information on dividend.	You can enter in dividend any real number or any expression that evaluates to a real number.	Expression, p. 1-880
divisor	The value by which the value in dividend is to be divided. The divisor value is the second argument that is supplied to the MOD() function. See "MOD() Function" for further information on divisor	You can enter in divisor any real number except zero or any expression that evaluates to a real number other than zero.	Expression, p. 1-880
exponent	The power to which the value that is specified in base is to be raised. The exponent value is the second argument that is supplied to the POW() function. See "POW() Function" for further information on exponent.	You can enter in exponent any real number or any expression that evaluates to a real number.	Expression, p. 1-880
index	The type of root to be returned, where 2 represents square root, 3 represents cube root, and so on. The index value is the second argument that is supplied to the ROOT() function. The default value of index is 2. See "ROOT() Function" for further information on index.	You can enter in index any real number except zero or any expression that evaluates to a real number other than zero.	Expression, p. 1-880
num_expression	A numeric expression for which an absolute value is to be returned. The expression serves as the argument for the ABS() function. See "ABS() Function" for further information on num_expression.	The value of num_expression can be any real number.	Expression, p. 1-880
radicand	An expression whose root value is to be returned. The radicand value is the first argument that is supplied to the ROOT() function. See "ROOT() Function" for further information on radicand.	You can enter in radicand any real number or any expression that evaluates to a real number.	Expression, p. 1-880
rounding factor	The number of digits to which a numeric expression is to be rounded. The rounding factor value is the second argument that is supplied to the ROUND() function. The default value of rounding factor is zero. This default means that the numeric expression is rounded to zero digits or the ones place. See "ROUND() Function" for further information on rounding factor.	The value you specify in rounding factor must be an integer between +32 and -32, inclusive. See "ROUND() Function" for further information on this restriction.	Literal Number, p. 1-1001
sqrt_radicand	An expression whose square root value is to be returned. The sqrt_radicand value is the argument that is supplied to the SQRT() function. See "SQRT() Function" for further information on sqrt_radicand.	You can enter in sqrt_radicand any real number or any expression that evaluates to a real number.	Expression, p. 1-880
truncating factor	The position to which a numeric expression is to be truncated. The truncating factor value is the second argument that is supplied to the TRUNC() function.The default value of truncating factor is zero. This default means that the numeric expression is truncated to zero digits or the ones place. See "TRUNC() Function" for further information on truncating factor.	The value you specify in truncating factor must be an integer between +32 and -32, inclusive. See "TRUNC() Function" for further information on this restriction.	Literal Number, p. 1-1001

ABS() Function

SELECT order_num, customer_num, ship_charge 

  FROM orders WHERE ABS(ship_charge) > 20

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:

SELECT MOD(today - MDY(1,1,year(today)),30) FROM orders

SELECT * FROM circles WHERE (3.1417 * POW(radius,2)) < 1000

SELECT ROOT(9) FROM angles         -- square root of 9



SELECT ROOT(64,3) FROM angles      -- cube root of 64

ROUND() Function

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.

Figure 1-3 ROUND() Function

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.

SELECT order_num , ROUND(total_price) FROM items

	WHERE ROUND(total_price) = 124.00

SELECT ROUND(125.46), ROUND(total_price) FROM items

The following example returns the square root of 9 for each row of the angles table:

SELECT SQRT(9) FROM angles

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.

Figure 1-4 TRUNC() Function

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.

SELECT TRUNC(125.46), TRUNC(total_price) FROM items

{3, 7, 9, 16, 0}

SELECT CARDINALITY(set_col)

	FROM table1

DBINFO() Function

• To locate the name of a dbspace corresponding to a tblspace number or expression
• To find out the last SERIAL value inserted in a table
• To find out the number of rows processed by selects, inserts, deletes, updates, and execute procedure statements
• To find out the session ID of the current session
• To find out the last SERIAL8 value inserted in a table

Using the 'DBSPACE' Option

SELECT tabname, partnum FROM systables;

SELECT DBINFO ('DBSPACE', 16777289) FROM systables;

Tip: To obtain the value of the last SERIAL8 value that is inserted into a table, use the 'serial8' option of DBINFO(). For more information, see page 1-911.

The following example uses the 'sqlca.sqlerrd1' option:

.

.

EXEC SQL create table fst_tab (ordernum serial, partnum int);

EXEC SQL create table sec_tab (ordernum serial);



EXEC SQL insert into fst_tab VALUES (0,1);

EXEC SQL insert into fst_tab VALUES (0,4);

EXEC SQL insert into fst_tab VALUES (0,6);



EXEC SQL insert into sec_tab values (dbinfo('sqlca.sqlerrd1'));
.

.

Using the 'sqlca.sqlerrd2' Option

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:

CREATE PROCEDURE del_rows (pnumb int)

RETURNING int;



DEFINE nrows int;



DELETE FROM sec_tab WHERE partnum=pnumb;

LET nrows = DBINFO('sqlca.sqlerrd2');

RETURN nrows;



END PROCEDURE

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.

SELECT DBINFO('sessionid') AS my_sessionid

	FROM systables

	WHERE tabname = 'systables'

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:

SELECT DBINFO('sessionid') AS user_sessionid

	FROM customer

	where customer_num = 101

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:

CREATE PROCEDURE get_sess()

RETURNING INT;

RETURN DBINFO('sessionid');

END PROCEDURE;

Tip: To obtain the value of the last SERIAL value that is inserted into a table, use the 'sqlca.sqlerrd1' option of DBINFO(). For more information, see page 1-911.

The following example uses the 'serial8' option:

.

.

EXEC SQL create table fst_tab 

	(ordernum serial8, partnum int);

EXEC SQL create table sec_tab (ordernum serial8);



EXEC SQL insert into fst_tab VALUES (0,1);

EXEC SQL insert into fst_tab VALUES (0,4);

EXEC SQL insert into fst_tab VALUES (0,6);



EXEC SQL insert into sec_tab 

	select dbinfo('serial8')

	from sec_tab where partnum = 6;

Exponential and Logarithmic Functions

EXP() Function

SELECT EXP(3) FROM angles

SELECT LOGN(population) FROM history WHERE country='US'

	ORDER BY date

SELECT LOG10(distance) + 1 digits FROM travel

SELECT colname, coltype, HEX(collength)

	FROM syscolumns C, systables T

	WHERE C.tabid = T.tabid AND T.tabname = 'orders'

SELECT tabname, HEX(partnum) FROM systables

SELECT HEX(order_num + 1) FROM orders

LENGTH() Function

The following example illustrates the use of the LENGTH() function:

SELECT customer_num, LENGTH(fname) + LENGTH(lname),

	LENGTH('How many bytes is this?')

	FROM customer WHERE LENGTH(company) > 10 

ESQL

You can use the LENGTH() function to return the length of a character variable.

GLS

For information on GLS aspects of the LENGTH function, see the Guide to GLS Functionality.

OCTET_LENGTH() Function

CHAR_LENGTH() Function

Shared-Library Functions

Element	Purpose	Restrictions	Syntax
language	A character string that specifies the language of the user-defined routines in the module pathname shared library.	Must be either "c" (for external routines) or "spl" (for SPL routines).	Quoted String, p. 1-1014
module pathname	The full pathname of the shared library that you want to reload.	The shared library must exist with the specified pathname.	Quoted String, p. 1-1014
new module pathname	The full pathname of the new shared library to replace the shared library that old module pathname specifies.	The shared library must exist with the specified pathname.	Quoted String, p. 1-1014
old module pathname	The full pathname of the shared library to replace with the shared library that new module path specifies.	The shared library must exist with the specified pathname.	Quoted String, p. 1-1014

IFX_RELOAD_MODULE Function

• Zero (0) to indicate success
• A negative integer to indicate an error

EXECUTE FUNCTION ifx_reload_module("/usr/apps/opaque_types/circle.so", 

	"c")

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

• Zero (0) to indicate success
• A negative integer to indicate an error

EXECUTE FUNCTION ifx_replace_module("/usr/apps/opaque_types/circle.so", 
	"/usr/apps/shared_libs/circle.so", "c")

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

(1 of 2)

Element	Purpose	Restrictions	Syntax
BLOB column	The name of a column of type BLOB	If you specify the table name and column name, a BLOB column must exist in that table.	Identifier, p. 1-966
CLOB column	The name of a column of type CLOB	If you specify the table name and column name, a CLOB column must exist in that table.	Identifier, p. 1-966
column name	The name of a column within table name whose storage characteristics are used for the copy of the BLOB or CLOB value	This column must have CLOB or BLOB as its data type.	Identifier, p. 1-966
file destination	The string "server" or "client" to indicate the computer on which to put or get the smart large object	The only valid values are the strings "server" or "client".	Quoted String, p. 1-1014
pathname	The directory path and filename to locate the smart large object. See the example on page 1-926.	The pathname must exist on the computer designated by file destination.	Quoted String, p. 1-1014
table name	The name of the table that contains column name, whose storage characteristics are used for the copy of the BLOB or CLOB value	The table must exist in the database and it must contain a CLOB or BLOB column.	Identifier, p. 1-966

• 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.

• If you omit table name and column name, FILETOBLOB creates a BLOB value with the system-specified storage defaults and 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 INFORMIX-Universal Server Administrator's Guide.

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

GLS

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'))

LOTOFILE Function

• 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.

file.hex_id

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

'/tmp/resume'

/tmp/resume.203b2

• 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:

'/tmp/resume.txt!'

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

/tmp/resume.txt

GLS

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

• If you omit table name and column name, 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.

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.

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

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 interview 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 interview 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;

Time Functions

(1 of 2)

Element	Purpose	Restrictions	Syntax
date/datetime expression	An expression that serves as an argument in the following functions: DAY(), MONTH(), WEEKDAY(), YEAR(), and EXTEND()	The expression must evaluate to a DATE or DATETIME value.	Expression, p. 1-880
day integer expression	An expression that represents the number of the day of the month	The expression must evaluate to an integer not greater than the number of days in the specified month.	Expression, p. 1-880
first	A qualifier that specifies the first field in the result. If you do not specify first and last qualifiers, the default value of first is YEAR.	The qualifier can be any DATETIME qualifier, as long as it is larger than last.	DATETIME Field Qualifier, p. 1-878
last	A qualifier that specifies the last field in the result. If you do not specify first and last qualifiers, the default value of last is FRACTION(3).	The qualifier can be any DATETIME qualifier, as long as it is smaller than first.	DATETIME Field Qualifier, p. 1-878
month integer expression	An expression that represents the number of the month	The expression must evaluate to an integer between 1 and 12, inclusive.	Expression, p. 1-880
non-date expression	An expression whose value is to be converted to a DATE data type	You can specify any expression that can be converted to a DATE data type. Usually you specify an expression that evaluates to a CHAR, DATETIME, or INTEGER value.	Expression, p. 1-880
year integer expression	An expression that represents the year	The expression must evaluate to a four-digit integer. You cannot use a two-digit abbreviation.	Expression, p. 1-880

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

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

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)

DAY() Function

WHERE DAY(order_date) > DAY(CURRENT)

SELECT order_num, MONTH(order_date) FROM orders

SELECT * FROM orders

	WHERE WEEKDAY(paid_date) = WEEKDAY(CURRENT)

SELECT order_num, customer_num FROM orders

	WHERE year(ship_date) < YEAR(TODAY)

EXTEND() Function

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.

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 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'

Formulas for Radian Expressions

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

# degrees * p/180= # radians

# radians * 180/p = # degrees

SELECT COS(degrees*180/3.1417) FROM anglestbl

SELECT SIN(radians) FROM anglestbl

SELECT TAN(radians) FROM anglestbl

SELECT ACOS(-0.73) FROM anglestbl

SELECT ASIN(-0.73) FROM anglestbl

SELECT ATAN(-0.73) FROM anglestbl

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

                                compares to angles

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

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

TRIM() Function

Element	Purpose	Restrictions	Syntax
trim character value expression	An expression that evaluates to a single character or null	This expression must be a character expression.	Quoted String, p. 1-1014
source character value expression	An arbitrary character string expression, including a column or another TRIM() function	This expression cannot be a host variable.	Quoted String, p. 1-1014

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;

Fixed Character Columns

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.

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




Figure 1-6 

Result of TRIM() Operation





 

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

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:

read_address('Miller')

read_address(lastname = 'Miller')

Element	Purpose	Restrictions	Syntax
slv name	The name of a statement local variable you are defining.	The slv name exists only for the life of the statement. The slv name must be unique within the statement.	Identifier, p. 1-966
opaque data type	The name of an opaque data type.	The opaque data type must already exist in the database.	Identifier, p. 1-966
distinct data type	The name of a distinct data type.	The distinct data type must already exist in the database.	Identifier, p. 1-966

Statement Local Variable Declaration

Statement Local Variable Expression

Element	Purpose	Restrictions	Syntax
slv name	The name of a statement local variable that has been defined.	The slv name exists only for the life of the statement. The slv name must be unique within the statement.	Identifier, p. 1-966

To use a Statement Local Variable with a call to a user-defined function, you must take three steps:

• Declare an OUT parameter when you register the function with CREATE FUNCTION.
• Declare the Statement Local Variable in a function expression in the WHERE clause, using the syntax of the Statement Local Variable Declaration.
• Use the Statement Local Variable in the WHERE clause, with the syntax of the Statement Local Variable Expression.

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

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;

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.

Important: A Statement Local Variable is valid only for the life of a single SQL statement.

For more information on OUT parameters and Statement Local Variables, see the Extending INFORMIX-Universal Server: User-Defined Routines manual.

Aggregate Expressions

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.

Element	Purpose	Restrictions	Syntax
column name	The name of the column to which the specified aggregate function is applied	If you specify an aggregate expression and one or more columns in the SELECT clause of a SELECT statement, you must put all the column names that are not used within the aggregate expression or a time expression in the GROUP BY clause. You cannot apply an aggregate function to a BYTE or TEXT column. See "Subset of Expressions Allowed in an Aggregate Expression" for other general restrictions. For restrictions that depend on the keywords that precede column name, see the headings for individual keywords on the following pages.	Identifier, p. 1-966

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

Subset of Expressions Allowed in an Aggregate Expression

• 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 originating 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

• AVG
• SUM
• MIN
• MAX

Including or Excluding Duplicates in the Row Set

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

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: Syntax.

COUNT(*) Function

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

SELECT COUNT(*) FROM stock

SELECT fname, COUNT(*) FROM customer

	GROUP BY fname

	HAVING COUNT(*) > 1

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.

SELECT COUNT(*) AS total_rows FROM orders

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.

SELECT COUNT (*) AS no_ship_instruct

	FROM orders

	WHERE ship_instruct IS NULL

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.

SELECT COUNT (*) AS ship_express

	FROM ORDERS

	WHERE ship_instruct = 'express'

COUNT DISTINCT and UNIQUE Keywords

SELECT COUNT (DISTINCT item_num) FROM items

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:

SELECT COUNT (UNIQUE item_num) FROM items

SELECT COUNT(DISTINCT ship_instruct) AS unique_notnulls

	FROM orders

COUNT column name Option

SELECT COUNT (item_num) FROM items

The following example shows how to include the ALL keyword in the COUNT column name option:

SELECT COUNT (ALL item_num) FROM items

SELECT COUNT(ship_instruct) AS total_notnulls

	FROM orders

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.

SELECT COUNT (ALL ship_instruct) AS all_notnulls

	FROM orders

AVG() Function

SELECT AVG(unit_price) FROM stock WHERE stock_num = 110

MAX() Function

SELECT MAX(unit_price) FROM stock

	WHERE NOT EXISTS (SELECT * FROM items 

		WHERE stock.stock_num = items.stock_num AND

		stock.manu_code = items.manu_code)

MIN() Function

SELECT MIN(unit_price) FROM stock

SUM() Function

SELECT SUM(total_price) FROM items WHERE order_num = 1013

You cannot use the SUM() function with a character column.

RANGE() Function

range(expr) = max(expr) - min(expr)

SELECT RANGE(age) FROM u_pop

SELECT RANGE(age) FROM u_pop

	GROUP BY birth

Important: All computations for the RANGE function are performed in 32-digit precision, which should be sufficient for many sets of input data. The computation, however, loses precision or returns incorrect results when all of the input data values have 16 or more digits of precision.

STDEV() Function

You can apply the STDEV() function only to numeric columns. The following query finds the standard deviation on a population:

SELECT STDEV(age) FROM u_pop WHERE u_pop.age > 0

SELECT STDEV(age) FROM u_pop

	GROUP BY birth

	WHERE STDEV(age) > 0

Important: All computations for the STDEV() function are performed in 32-digit precision, which should be sufficient for many sets of input data. The computation, however, loses precision or returns incorrect results when all of the input data values have 16 or more digits of precision.

VARIANCE() Function

(SUM(Xi**2) - (SUM(Xi)**2)/N)/(N-1)

SELECT VARIANCE(age) FROM u_pop WHERE u_pop.age > 0

SELECT VARIANCE(age) FROM u_pop

	GROUP BY birth

	WHERE VARIANCE(age) > 0

Important: All computations for the VARIANCE() function are performed in 32-digit precision, which should be sufficient for many sets of input data. The computation, however, loses precision or returns incorrect results when all of the input data values have 16 or more digits of precision.

Summary of Aggregate Function Behavior

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:

SELECT AVG(a_number) AS average_number 

	FROM testtable

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.

(1 of 2)

Function	Results
COUNT(*)	7
AVG	2.66666666666667
AVG (DISTINCT)	3.00000000000000
MAX	4
MAX(DISTINCT)	4
MIN	2
MIN(DISTINCT)	2
SUM	16
SUM(DISTINCT)	9
COUNT(DISTINCT)	3
COUNT(ALL)	6
RANGE	2
STDEV	0.81649658092773
VARIANCE	0.66666666666667

Error Checking with Aggregate Functions

You can also use the GET DIAGNOSTICS statement for error checking. See the GET DIAGNOSTICS statement in this manual.

Using Arithmetic Operators with Expressions

The following examples use binary arithmetic operators:

quantity * total_price 



price * 2 



COUNT(*) + 2 

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

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:

Arithmetic Operation	Arithmetic Operator	Operator Function
Positive	+	positive()
Negative	-	negate()

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:

SELECT order_num, ship_charge/ship_weight FROM orders

	WHERE order_num = 1023

References

In the Guide to GLS Functionality, see the discussions of column expressions, the discussion of length functions, and the discussion of the TRIM() function.