<i>Informix Guide to SQL: Syntax</i>

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book

SQL Statements

SELECT

Use the SELECT statement to query a database or the contents of an SPL or INFORMIX-ESQL/C collection variable.

(1 of 2)

Element Purpose Restrictions Syntax
*
The asterisk (*) signifies that all columns in the specified table or view are to be selected.
Use this symbol whenever you want to retrieve all the columns in the table or view in their defined order. If you want to retrieve all the columns in some other order, or if you want to retrieve a subset of the columns, you must specify the columns explicitly in the SELECT list.
The asterisk (*) is a literal value that has a special meaning in this statement.

alias
A temporary alternative name for a table or view within the scope of a SELECT statement. You can use aliases to make a query shorter.
You must specify a table name and the table alias in the FROM clause. See "FROM Clause" for further information on this restriction.
Identifier, p. 1-966

column name
The name of a column from one of the tables or views to be joined. Rows from the tables or views are joined when there is a match between the values of the specified columns.
When the specified columns have the same name in the tables or views to be joined, you must distinguish the columns by preceding each column name with the name or alias of the table or view in which the column resides.
Identifier, p. 1-966

display label
A temporary name that you assign to a column. In DB-Access, the display label appears as the heading for the column in the output of the SELECT statement. In ESQL, the value of display label is stored in the sqlname field of the sqlda structure. For more information on the display label parameter, see "Using a Display Label".
You can assign a display label to any column in your select list. If you are creating a temporary table with the SELECT...INTO TEMP clause, you must supply a display label for any columns that are not simple column expressions. The display label is used as the name of the column in the temporary table. If you are using the SELECT statement in creating a view, do not use display labels. Specify the desired label names in the CREATE VIEW column list instead. If your display label is also a keyword, you can use the AS keyword with the display label to clarify the use of the word. You must use the AS keyword with the display label to use any of the following words as a display label: UNITS, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, or FRACTION.
Identifier, p. 1-966

field name
The name of the row field that you are accessing in the row column
The field must be a component of the row that row-column name or field name (for nested rows) specifies.
Identifier, p. 1-966

row-column name
The name of the row column that you specify
The column must be a named row type or unnamed row type.
Identifier, p. 1-966

In the SELECT clause, specify exactly what data is being selected as well as whether you want to omit duplicate values.

Allowing Duplicates

You can apply the ALL, UNIQUE, or DISTINCT keywords to indicate whether duplicate values are returned, if any exist. If you do not specify any keywords, all the rows are returned by default.


Keyword	Meaning
`ALL`	Specifies that all selected values are returned, regardless of whether duplicates exist. `ALL` is the default state.
`DISTINCT`	Eliminates duplicate rows from the query results
`UNIQUE`	Eliminates duplicate rows from the query results. `UNIQUE` is a synonym for `DISTINCT`.

For example, the following query lists the stock_num and manu_code of all items that have been ordered, excluding duplicate items:

SELECT DISTINCT stock_num, manu_code FROM items

You can use the DISTINCT or UNIQUE keywords once in each level of a query or subquery. For example, the following query uses DISTINCT in both the query and the subquery:

SELECT DISTINCT stock_num, manu_code FROM items

	WHERE order_num = (SELECT DISTINCT order_num FROM orders

		WHERE customer_num = 120)

Expressions in the Select List

You can use any basic type of expression (column, constant, function, aggregate function, and procedure), or combination thereof, in the select list. The expression types are described in "Expression".

The following sections present examples of using each type of simple expression in the select list.

You can combine simple numeric expressions by connecting them with arithmetic operators for addition, subtraction, multiplication, and division. However, if you combine a column expression and an aggregate function, you must include the column expression in the GROUP BY clause.

You cannot use variable names (for example, host variables in an external application or SPL variables) in the select list by themselves. You can include a variable name in the select list, however, if an arithmetic or concatenation operator connects it to a constant.

Selecting Columns

Column expressions are the most commonly used expressions in a SELECT statement. See "Column Expressions" for a complete description of the syntax and use of column expressions.

The following examples show column expressions within a select list:

SELECT orders.order_num, items.price FROM orders, items



SELECT customer.customer_num ccnum, company FROM customer



SELECT catalog_num, stock_num, cat_advert [1,15] FROM catalog



SELECT lead_time - 2 UNITS DAY FROM manufact

Selecting Constants

If you include a constant expression in the select list, the same value is returned for each row that the query returns. See "Constant Expressions" for a complete description of the syntax and use of constant expressions.

The following examples show constant expressions within a select list:

SELECT 'The first name is', fname FROM customer



SELECT TODAY FROM cust_calls



SELECT SITENAME FROM systables WHERE tabid = 1



SELECT lead_time - 2 UNITS DAY FROM manufact



SELECT customer_num + LENGTH('string') from customer

Selecting Function Expressions

A function expression uses a function that is evaluated for each row in the query. All function expressions require arguments. This set of expressions contains the time functions and the length function when they are used with a column name as an argument.

The following examples show function expressions within a select list:

SELECT EXTEND(res_dtime, YEAR TO SECOND) FROM cust_calls



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



SELECT HEX(order_num) FROM orders



SELECT MONTH(order_date) FROM orders

Selecting Aggregate Expressions

An aggregate function returns one value for a set of queried rows. The aggregate functions take on values that depend on the set of rows that the WHERE clause of the SELECT statement returns. In the absence of a WHERE clause, the aggregate functions take on values that depend on all the rows that the FROM clause forms.

The following examples show aggregate functions in a select list:

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

Selecting SPL Function Expressions

SPL functions extend the range of functions that are available to you and allow you to perform a subquery on each row that you select. The following example calls the get_orders function for each customer_num and displays the output of the function under the n_orders label:

SELECT customer_num, lname, get_orders(customer_num) n_orders

	FROM customer

Selecting Expressions That Use Arithmetic Operators

You can combine numeric expressions with arithmetic operators to make complex expressions. You cannot combine expressions that contain aggregate functions with column expressions. The following examples show expressions that use arithmetic operators within a select list:

SELECT stock_num, quantity*total_price FROM customer



SELECT price*2 doubleprice FROM items



SELECT count(*)+2 FROM customer 



SELECT count(*)+LENGTH('ab') FROM customer

Selecting Row Fields

You can select a particular field of a row-type column (named or unnamed row type) with dot notation, which uses a period (.) as a separator between the row and field names. For example, suppose you have the following table structure:

CREATE ROW TYPE one (a INTEGER, b FLOAT);

CREATE ROW TYPE two (c one, d CHAR(10));

CREATE ROW TYPE three (e CHAR(10), f two);


CREATE TABLE new_tab OF TYPE two;

CREATE TABLE three_tab OF TYPE three;

The following expressions are valid in the select list:

SELECT t.c FROM new_tab t;

SELECT f.c.a FROM three_tab;

SELECT f.d FROM three_tab;

For more information, see "Column Expressions" in the Expression segment.

Using a Display Label

If you are creating a temporary table, you must supply a display label for any columns that are not simple column expressions. The display label is used as the name of the column in the temporary table.

DB

A display label appears as the heading for that column in the output of the SELECT statement.

ESQL

The value of display label is stored in the sqlname field of the sqlda structure. See your SQL API product manual for more information on the sqlda structure.

If you are using the SELECT statement in creating a view, do not use display labels. Specify the desired label names in the CREATE VIEW column list instead.

Using the AS Keyword

If your display label is also a keyword, you can use the AS keyword with the display label to clarify the use of the word. If you want to use the word UNITS, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, or FRACTION as your display label, you must use the AS keyword with the display label. The following example shows how to use the AS keyword with minute as a display label:

SELECT call_dtime AS minute FROM cust_calls

INTO Clause

Use the INTO clause within a routine to specify the program variables or host variables to receive the data that the SELECT statement retrieves. The following diagram shows the syntax of the INTO clause.

Element Purpose Restrictions Syntax
data
variable
A variable that receives the value returned by a function
If you issue this statement within an ESQL/C program, the receiving variable must be a host variable.
If you issue this statement within an SPL routine, the receiving variable must be an SPL variable.
If you issue this statement within a CREATE TRIGGER statement, the receiving variables must be column names within the triggering table or another table.
The name of a receiving variable must conform to language-specific rules for variable names.
For the syntax of SPL variables, see Identifier, p. 1-966.
For the syntax of column names, see Identifier, p. 1-966.

data structure
A structure that has been declared as a host variable
The individual elements of the structure must be matched appropriately to the data type of values being selected.
The name of the data structure must conform to language-specific rules for data structures.

indicator variable
A program variable that receives a return code if null data is placed in the corresponding data variable
This parameter is optional, but you should use an indicator variable if the possibility exists that the value of the corresponding data variable is null.
The name of the indicator variable must conform to language-specific rules for indicator variables.

You must specify an INTO clause with SELECT to name the variables that receive the values that the query returns. If the query returns more than one value, the values are returned into the list of variables in the order in which you specify them.

If the SELECT statement stands alone (that is, it is not part of a DECLARE statement and does not use the INTO clause), it must be a singleton SELECT statement. A singleton SELECT statement returns only one row. The following example shows a SELECT statement in INFORMIX-ESQL/C:

EXEC SQL select fname, lname, company_name

	into :p_fname, :p_lname, :p_coname

	where customer_num = 101;

INTO Clause with Indicator Variables

ESQL

You should use an indicator variable if the possibility exists that data returned from the SELECT statement is null. See your SQL API product manual for more information about indicator variables.

ESQL

INTO Clause with Cursors

If the SELECT statement returns more than one row, you must use a select cursor in a FETCH statement to fetch the rows individually. You can put the INTO clause in the FETCH statement rather than in the SELECT statement, but you cannot put it in both.

E/C

In an INFORMIX-ESQL/C program, use the DECLARE statement to declare the function cursor and the FETCH statement to fetch the rows individually from the function cursor. The following INFORMIX-ESQL/C code examples show different ways you can use the INTO clause:

Using the INTO clause in the SELECT statement

EXEC SQL declare q_curs cursor for

	select lname, company

		into :p_lname, :p_company

		from customer;

EXEC SQL open q_curs;

while (SQLCODE == 0)

	EXEC SQL fetch q_curs;

EXEC SQL close q_curs;

Using the INTO clause in the FETCH statement

EXEC SQL declare q_curs cursor for

	select lname, company

	from customer;

EXEC SQL open q_curs;

while (SQLCODE == 0)

	EXEC SQL fetch q_curs into :p_lname, :p_company;

EXEC SQL close q_curs;

SPL

In an SPL routine, if a SELECT returns more than one row, you must use the FOREACH statement to access the rows individually. The INTO clause of the SELECT statement holds the fetched values. For more information, see the FOREACH statement on page 2-27.

Preparing a SELECT...INTO Query

ESQL

You cannot prepare a query that has an INTO clause. You can prepare the query without the INTO clause, declare a cursor for the prepared query, open the cursor, and then use the FETCH statement with an INTO clause to fetch the cursor into the program variable. Alternatively, you can declare a cursor for the query without first preparing the query and include the INTO clause in the query when you declare the cursor. Then open the cursor, and fetch the cursor without using the INTO clause of the FETCH statement.

Using Array Variables with the INTO Clause

ESQL

If you use a DECLARE statement with a SELECT statement that contains an INTO clause, and the program variable is an array element, you can identify individual elements of the array with integer constants or with variables. The value of the variable that is used as a subscript is determined when the cursor is declared, so afterward the subscript variable acts as a constant.

The following INFORMIX-ESQL/C code example declares a cursor for a SELECT...INTO statement using the variables i and j as subscripts for the array a. After you declare the cursor, the INTO clause of the SELECT statement is equivalent to INTOa[5],a[2].

i = 5

j = 2

EXEC SQL declare c cursor for

	select order_num, po_num into :a[i], :a[j] from orders

		where order_num =1005 and po_num =2865

You can also use program variables in the FETCH statement to specify an element of a program array in the INTO clause. With the FETCH statement, the program variables are evaluated at each fetch rather than when you declare the cursor.

Error Checking

ESQL

If the number of variables that are listed in the INTO clause differs from the number of items in the SELECT clause, a warning is returned in the sqlwarn3 field of the sqlca.sqlwarn structure. The actual number of variables that are transferred is the lesser of the two numbers. See the INFORMIX-ESQL/C Programmer's Manual for information about the sqlwarn structure.

ANSI

If the number of variables that are listed in the INTO clause differs from the number of items in the SELECT clause, you receive an error.

If the data type of the receiving variable does not match that of the selected item, the data type of the selected item is converted, if possible. If the conversion is impossible, an error occurs, and a negative value is returned in the SQLCODE (sqlca.sqlcode) status variable. In this case, the value in the program variable is unpredictable.

Tip: When you encounter an SQLCODE error, a corresponding SQLSTATE error might also exist. See the GET DIAGNOSTICS statement for information about the SQLSTATE status variable.

FROM Clause

The FROM clause lists the table or tables from which you are selecting the data. The following diagram shows the syntax of the FROM clause.


Element	Purpose	Restrictions	Syntax
alias	A temporary alternative name for a table or view within the scope of a `SELECT` statement. You can use aliases to make a query shorter.	If the `SELECT` statement is a self-join, you must list the table name twice in the `FROM` clause and assign a different alias to each occurrence of the table name. If you use a potentially ambiguous word as an alias, you must precede the alias with the keyword `AS`. See "AS Keyword with Aliases" for further information on this restriction. Aliasing is not allowed for a collection of `ROW` types.	Identifier, p. 1-966

Usage

Use the keyword OUTER to form outer joins. Outer joins preserve rows that otherwise would be discarded by simple joins. See Chapter 3 of the Informix Guide to SQL: Tutorial for more information on outer joins.

The FROM clause cannot have a join when one of the tables to be joined is a collection.

If you use the SELECT statement to query a supertable, rows from both the supertable and its subtables are returned. To query rows from the supertable only, you must include the ONLY keyword in the FROM clause, as shown in the following example:

SELECT *
FROM ONLY(super_tab)

You can supply an alias for a table name or view name. You can use the alias to refer to the table or view in other clauses of the SELECT statement. This is especially useful with a self-join. (See the WHERE clause on page 1-620 for more information about self-joins.)

The following example shows typical uses of the FROM clause. The first query selects all the columns and rows from the customer table. The second query uses a join between the customer and orders table to select all the customers who have placed orders.

SELECT * FROM customer



SELECT fname, lname, order_num

	FROM customer, orders

	WHERE customer.customer_num = orders.customer_num

The following example is the same as the second query in the preceding example, except that it establishes aliases for the tables in the FROM clause and uses them in the WHERE clause:

SELECT fname, lname, order_num

	FROM customer c, orders o

	WHERE c.customer_num = o.customer_num

The following example uses the OUTER keyword to create an outer join and produce a list of all customers and their orders, regardless of whether they have placed orders:

SELECT c.customer_num, lname, order_num

	FROM customer c, OUTER orders o

	WHERE c.customer_num = o.customer_num

AS Keyword with Aliases

To use potentially ambiguous words as an alias for a table or view, you must precede them with the keyword AS. Use the AS keyword if you want to use the words ORDER, FOR, AT, GROUP, HAVING, INTO, UNION, WHERE, WITH, CREATE, or GRANT as an alias for a table or view.

E/C

Selecting From a Collection Variable

The SELECT statement with the Collection Derived Table segment allows you to select elements from a collection variable. The Collection Derived Table segment identifies the collection variable from which to select the elements. For more information on the Collection Derived Table segment, see page 1-831.

E/C

In an INFORMIX-ESQL/C program, declare a host variable of type collection for a collection variable. This collection variable can be typed or untyped.

SPL

In an SPL routine, declare a variable of type COLLECTION, LIST, MULTISET, or SET for a collection variable. This collection variable can be typed or untyped.

To select elements, follow these steps:

1. Create a collection variable in your SPL routine or ESQL/C program.

2. Optionally, fill the collection variable with elements.

SELECT

INSERT

3. Select a collection element from the collection variable with the SELECT statement and the Collection Derived Table segment.

4. Once the collection variable contains the correct elements, you can then use the INSERT or UPDATE statement on a table or view name to save the contents of the collection variable in a collection column (SET, MULTISET, or LIST).

The SELECT statement and the Collection Derived Table segment allow you to perform the following operations on a collection variable:

Select one element from the collection

SELECT

Select one or more elements into the collection

SELECT

E/C

For information on how to use a collection cursor to select one or more elements from an ESQL/C collection variable, see

"Associating a Cursor With a Collection Variable"

DECLARE

SPL

For information on how to use a collection cursor to select one or more elements from an SPL collection variable, see

"Using a SELECT...INTO Statement"

FOREACH

The SELECT statement and the Collection Derived Table segment allow you to select one element into a collection. The INTO clause identifies the variable for the element value that is selected from the collection variable. The data type of the host variable in the INTO clause must be compatible with the element type of the collection.

The SELECT statement on a collection variable has the following restrictions:

The select list of the SELECT statement cannot contain expressions.
The select list must be an asterisk (*) if the collection contains elements of opaque, distinct, built-in, or other collection data types.
Column names in the select list must be simple column names.

These columns cannot use the following syntax:

database@server:table.column

The following SELECT clauses and options are not allowed: GROUP BY, HAVING, INTO TEMP, ORDER BY, WHERE, and WITH REOPTIMIZATION
The FROM clause has no provisions to do a join.

SPL

In addition to the preceding list of restrictions, a SELECT...INTO that is associated with the FOREACH statement (called a collection query) has the following restrictions:

Its general structure is SELECT ... INTO ... FROM TABLE. The statement selects one element at a time from a collection variable named after the TABLE keyword into another variable called an element variable.
You must use a collection query within a FOREACH loop.
You cannot use the WITH HOLD option on the FOREACH statement.
The data type of the element variable must be the same as the element type of the collection.
The element variable can have any opaque, distinct, or collection data type, or any built-in data type except SERIAL, SERIAL8, TEXT, BYTE, CLOB or BLOB.

For more information a collection query, see the description of the FOREACH statement on page 2-27. For more information on how to use SPL routines to handle collections, see Chapter 14 in the Informix Guide to SQL: Tutorial.

If the element of the collection is itself a complex type (collection or row type), the collection is a nested collection. For example, suppose the ESQL/C collection variable, a_set, is a nested collection that is defined as follows:

EXEC SQL BEGIN DECLARE SECTION;

	client collection set(list(integer not null)) a_set;

	client collection list(integer not null) a_list;
	int an_int;

EXEC SQL END DECLARE SECTION;

To access the elements (or fields) of a nested collection, use a collection or row variable that matches the element type (a_list and an_int in the preceding code fragment) and a select cursor.

The following ESQL/C program uses a collection variable as a collection derived table:

main

{
	EXEC SQL BEGIN DECLARE SECTION;

		int a;

		client collection b;

	EXEC SQL END DECLARE SECTION;

	EXEC SQL allocate collection :b;
	EXEC SQL select set_col into :b from table1

		where int_col = 6;



	EXEC SQL declare set_curs cursor for

		select * from table(:b)

		for update;

	EXEC SQL open set_curs;

	while (SQLCODE != SQLNOTFOUND)

	{

		EXEC SQL fetch set_curs into :a;
		if (a = 4)

		{

			EXEC SQL update table(:b)(x)

				set x = 10

				where current of set_curs;

			break;

		}

	}

	EXEC SQL update table1 set set_col = :b

		where int_col = 6;

	EXEC SQL deallocate collection :b;

	EXEC SQL close set_curs;

	EXEC SQL free set_curs;

}

For information on how to use collection host variables in an ESQL/C program, see the discussion of complex data types in the INFORMIX-ESQL/C Programmer's Manual.

You can modify the collection variable with the Collection Derived Table segment and the INSERT, UPDATE, or DELETE statements. The collection variable stores the elements of the collection. However, it has no intrinsic connection with a database column. Once the collection variable contains the correct elements, you must then save the collection variable in the collection column with one of the following statements:

To update the collection column in the table with the collection variable, use an UPDATE statement on a table or view name and specify the collection variable in the SET clause.

For more information, see

"Updating Collection Columns"

UPDATE

To insert a collection in a column, use the INSERT statement on a table or view name and specify the collection variable in the VALUES clause.

For more information, see

"Inserting Values into Collection Columns"

INSERT

Selecting From a Row Variable

E/C

The SELECT statement with the Collection Derived Table segment allows you to select fields from a row variable. The Collection Derived Table segment identifies the row variable from which to select the fields. For more information on the Collection Derived Table segment, see page 1-831.

To select fields, follow these steps:

1. Create a row variable in your ESQL/C program.

2. Optionally, fill the row variable with field values.

row

SELECT

row

UPDATE

3. Select row fields from the row variable with the SELECT statement and the Collection Derived Table segment.

4. Once the row variable contains the correct field values, you can then use the INSERT or UPDATE statement on a table or view name to save the contents of the row variable in a row column (named and unnamed).

The SELECT statement and the Collection Derived Table segment allow you to select a particular field or group of fields in the row variable. The INTO clause identifies the variable that holds the field value selected from the row variable. The data type of the host variable in the INTO clause must be compatible with the field type.

For example, the following code fragment puts the value of the width field into the rect_width host variable:

EXEC SQL BEGIN DECLARE SECTION;

	row (x int, y int, length float, width float) myrect;

	double rect_width;

EXEC SQL END DECLARE SECTION;

...

EXEC SQL select rect into :myrect from rectangles

	where area = 200;

EXEC SQL select width into :rect_width from table(:myrect);

The SELECT statement on a row variable has the following restrictions:

No expressions are allowed in the select list.
Row columns cannot be specified in a comparison condition in a WHERE clause.
The select list must be an asterisk (*) if the row-type contains fields of opaque, distinct, or built-in data types.
Column names in the select list must be simple column names.

database

server

table

column

The following SELECT clauses are not allowed: GROUP BY, HAVING, INTO TEMP, ORDER BY, and WHERE
The FROM clause has no provisions to do a join.

You can modify the row variable with the Collection Derived Table segment of the UPDATE statements. (The INSERT and DELETE statements do not support a row variable in the Collection Derived Table segment.) The row variable stores the fields of the row. However, it has no intrinsic connection with a database column. Once the row variable contains the correct field values, you must then save the variable into the row column with one of the following SQL statements:

To update the row column in the table with the row variable, use an UPDATE statement on a table or view name and specify the row variable in the SET clause.

For more information, see

"Updating Row-Type Columns"

UPDATE

To insert a row in a column, use the INSERT statement on a table or view name and specify the row variable in the VALUES clause.

For more information, see

"Inserting Values into Row-Type Columns"

INSERT

For more information on how to use SPL row variables, see Chapter 14 of the Informix Guide to SQL: Tutorial. For more information on how to use ESQL/C row variables, see the discussion of complex data types in the INFORMIX-ESQL/C Programmer's Manual.

WHERE Clause

Use the WHERE clause to specify search criteria and join conditions on the data that you are selecting.

Using a Condition in the WHERE Clause

You can use the following kinds of simple conditions or comparisons in the WHERE clause:

Relational-operator condition
BETWEEN
IN
IS NULL
LIKE or MATCHES

You also can use a SELECT statement within the WHERE clause; using a SELECT statement this way is called a subquery. The following list contains the kinds of subquery WHERE clauses:

IN
EXISTS
ALL/ANY/SOME

Examples of each type of condition are shown in the following sections. For more information about each kind of condition, see the Condition segment on page 1-835.

You cannot use an aggregate function in the WHERE clause unless it is part of a subquery or if the aggregate is on a correlated column that originates from a parent query and the WHERE clause is within a subquery that is within a HAVING clause.

Relational-Operator Condition

For a complete description of the relational-operator condition, see page 1-840.

A relational-operator condition is satisfied when the expressions on either side of the relational operator fulfill the relation that the operator set up. The following SELECT statements use the greater than (>) and equal (=) relational operators:

SELECT order_num FROM orders

	WHERE order_date > '6/04/94'



SELECT fname, lname, company

	FROM customer

	WHERE city[1,3] = 'San'

BETWEEN Condition

For a complete description of the BETWEEN condition, see page 1-841.

The BETWEEN condition is satisfied when the value to the left of the BETWEEN keyword lies in the inclusive range of the two values on the right of the BETWEEN keyword. The first two queries in the following example use literal values after the BETWEEN keyword. The third query uses the CURRENT function and a literal interval. It looks for dates between the current day and seven days earlier.

SELECT stock_num, manu_code FROM stock

	WHERE unit_price BETWEEN 125.00 AND 200.00



SELECT DISTINCT customer_num, stock_num, manu_code

	FROM orders, items

	WHERE order_date BETWEEN '6/1/93' AND '9/1/93'



SELECT * FROM cust_calls WHERE call_dtime

	BETWEEN (CURRENT - INTERVAL(7) DAY TO DAY) AND CURRENT

IN Condition

For a complete description of the IN condition, see page 1-842.

The IN condition is satisfied when the expression to the left of the IN keyword is included in the list of values to the right of the keyword. The following examples show the IN condition:

SELECT lname, fname, company

	FROM customer

	WHERE state IN ('CA','WA', 'NJ')



SELECT * FROM cust_calls

	WHERE user_id NOT IN (USER )

IS NULL Condition

For a complete description of the IS NULL condition, see page 1-844.

The IS NULL condition is satisfied if the column contains a null value. If you use the NOT option, the condition is satisfied when the column contains a value that is not null. The following example selects the order numbers and customer numbers for which the order has not been paid:

SELECT order_num, customer_num FROM orders

	WHERE paid_date IS NULL

LIKE or MATCHES Condition

For a complete description of the LIKE or MATCHES condition, see page 1-844.

The LIKE or MATCHES condition is satisfied when either of the following tests is true:

The value of the column that precedes the LIKE or MATCHES keyword matches the pattern that the quoted string specifies. You can use wildcard characters in the string.
The value of the column that precedes the LIKE or MATCHES keyword matches the pattern that is specified by the column that follows the LIKE or MATCHES keyword. The value of the column on the right serves as the matching pattern in the condition.

The following SELECT statement returns all rows in the customer table in which the lname column begins with the literal string 'Baxter'. Because the string is a literal string, the condition is case sensitive.

SELECT * FROM customer WHERE lname LIKE 'Baxter%'

The following SELECT statement returns all rows in the customer table in which the value of the lname column matches the value of the fname column:

SELECT * FROM customer WHERE lname LIKE fname

The following examples use the LIKE condition with a wildcard. The first SELECT statement finds all stock items that are some kind of ball. The second SELECT statement finds all company names that contain a percent sign (%). The backslash (\) is used as the standard escape character for the wildcard percent sign (%). The third SELECT statement uses the ESCAPE option with the LIKE condition to retrieve rows from the customer table in which the company column includes a percent sign (%). The z character is used as an escape character for the wildcard percent sign (%).

SELECT stock_num, manu_code FROM stock 

	WHERE description LIKE '%ball'



SELECT * FROM customer

	WHERE company LIKE '%\%%'



SELECT * FROM customer

	WHERE company LIKE '%z%%' ESCAPE 'z'

The following examples use MATCHES with a wildcard in several SELECT statements. The first SELECT statement finds all stock items that are some kind of ball. The second SELECT statement finds all company names that contain an asterisk (*). The backslash(\) is used as the standard escape character for the wildcard asterisk (*). The third statement uses the ESCAPE option with the MATCHES condition to retrieve rows from the customer table where the company column includes an asterisk (*). The z character is used as an escape character for the wildcard asterisk (*).

SELECT stock_num, manu_code FROM stock 

	WHERE description MATCHES '*ball'



SELECT * FROM customer

	WHERE company MATCHES '*\**'



SELECT * FROM customer

	WHERE company MATCHES '*z**' ESCAPE 'z'

IN Subquery

For a complete description of the IN subquery, see page 1-849.

With the IN subquery, more than one row can be returned, but only one column can be returned. The following example shows the use of an IN subquery in a SELECT statement:

SELECT DISTINCT customer_num FROM orders

	WHERE order_num NOT IN

		(SELECT order_num FROM items

			WHERE stock_num = 1)

EXISTS Subquery

For a complete description of the EXISTS subquery, see page 1-850.

With the EXISTS subquery, one or more columns can be returned.

The following example of a SELECT statement with an EXISTS subquery returns the stock number and manufacturer code for every item that has never been ordered (and is therefore not listed in the items table). It is appropriate to use an EXISTS subquery in this SELECT statement because you need the correlated subquery to test both stock_num and manu_code in the items table.

SELECT stock_num, manu_code FROM stock

	WHERE NOT EXISTS

		(SELECT stock_num, manu_code FROM items

			WHERE stock.stock_num = items.stock_num AND

				stock.manu_code = items.manu_code)

The preceding example would work equally well if you use a SELECT * statement in the subquery in place of the column names because you are testing for the existence of a row or rows.

ALL/ANY/SOME Subquery

For a complete description of the ALL/ANY/SOME subquery, see page 1-850.

In the following example, the SELECT statements return the order number of all orders that contain an item whose total price is greater than the total price of every item in order number 1023. The first SELECT statement uses the ALL subquery, and the second SELECT statement produces the same result by using the MAX aggregate function.

SELECT DISTINCT order_num FROM items

	WHERE total_price > ALL (SELECT total_price FROM items

 		 WHERE order_num = 1023)



SELECT DISTINCT order_num FROM items

	WHERE total_price > SELECT MAX(total_price) FROM items

 		 WHERE order_num = 1023)

The following SELECT statements return the order number of all orders that contain an item whose total price is greater than the total price of at least one of the items in order number 1023. The first SELECT statement uses the ANY keyword, and the second SELECT statement uses the MIN aggregate function.

SELECT DISTINCT order_num FROM items

	WHERE total_price > ANY (SELECT total_price FROM items

 		 WHERE order_num = 1023)



SELECT DISTINCT order_num FROM items

	WHERE total_price > (SELECT MIN(total_price) FROM items

		WHERE order_num = 1023)

You can omit the keywords ANY, ALL, or SOME in a subquery if you know that the subquery returns exactly one value. If you omit ANY, ALL, or SOME, and the subquery returns more than one value, you receive an error. The subquery in the following example returns only one row because it uses an aggregate function:

SELECT order_num FROM items 

	WHERE stock_num = 9 AND quantity =

		(SELECT MAX(quantity) FROM items WHERE stock_num = 9)

Using a Join in the WHERE Clause

You join two tables when you create a relationship in the WHERE clause between at least one column from one table and at least one column from another table. The effect of the join is to create a temporary composite table where each pair of rows (one from each table) that satisfies the join condition is linked to form a single row. You can create two-table joins, multiple-table joins, and self-joins.

The following diagram shows the syntax for a join.

Element Purpose Restrictions Syntax
alias
The alias assigned to the table or view in the FROM clause. See "FROM Clause" for more information on aliases for tables and views.
If the tables to be joined are the same table (that is, if the join is a self-join), you must refer to each instance of the table in the WHERE clause by the alias assigned to that table instance in the FROM clause.
Identifier, p. 1-966

column name
The name of a column from one of the tables or views to be joined. Rows from the tables or views are joined when there is a match between the values of the specified columns.
When the specified columns have the same name in the tables or views to be joined, you must distinguish the columns by preceding each column name with the name or alias of the table or view in which the column resides.
Identifier, p. 1-966

Two-Table Joins

The following example shows a two-table join:

SELECT order_num, lname, fname

	FROM customer, orders

	WHERE customer.customer_num = orders.customer_num

Tip: You do not have to select the column where the two tables are joined.

Multiple-Table Joins

A multiple-table join is a join of more than two tables. Its structure is similar to the structure of a two-table join, except that you have a join condition for more than one pair of tables in the WHERE clause. When columns from different tables have the same name, you must distinguish them by preceding the name with its associated table or table alias, as in table.column. See "Table Name" for the full syntax of a table name.

The following multiple-table join yields the company name of the customer who ordered an item as well as the stock number and manufacturer code of the item:

SELECT DISTINCT company, stock_num, manu_code

	FROM customer c, orders o, items i

	WHERE c.customer_num = o.customer_num

		AND o.order_num = i.order_num

Self-Joins

You can join a table to itself. To do so, you must list the table name twice in the FROM clause and assign it two different table aliases. Use the aliases to refer to each of the "two" tables in the WHERE clause.

The following example is a self-join on the stock table. It finds pairs of stock items whose unit prices differ by a factor greater than 2.5. The letters x and y are each aliases for the stock table.

SELECT x.stock_num, x.manu_code, y.stock_num, y.manu_code

	FROM stock x, stock y

	WHERE x.unit_price > 2.5 * y.unit_price

Outer Joins

The following outer join lists the company name of the customer and all associated order numbers, if the customer has placed an order. If not, the company name is still listed, and a null value is returned for the order number.

SELECT company, order_num

	FROM customer c, OUTER orders o

	WHERE c.customer_num = o.customer_num

See Chapter 3 of the Informix Guide to SQL: Tutorial for more information about outer joins.

GROUP BY Clause

Use the GROUP BY clause to produce a single row of results for each group. A group is a set of rows that have the same values for each column listed

(1 of 2)
Element	Purpose	Restrictions	Syntax
alias	The alias assigned to a table or view in the `FROM` clause. See "FROM Clause" for more information on aliases for tables and views.	You cannot use an alias for a table or view in the `GROUP BY` clause unless you have assigned the alias to the table or view in the `FROM` clause.	Identifier, p. 1-966
column name	The name of a stand-alone column in the select list of the `SELECT` clause or the name of one of the columns joined by an arithmetic operator in the select list. The `SELECT` statement returns a single row of results for each group of rows that have the same value in column name.	See "Relationship of the GROUP BY Clause to the SELECT Clause". You cannot use a column whose data type is a collection in the `GROUP BY` clause.	Identifier, p. 1-966
select number	An integer that identifies a column or expression in the select list of the `SELECT` clause by specifying its order in the select list. The `SELECT` statement returns a single row of results for each group of rows that have the same value in the column or expression identified by select number.	See "Using Select Numbers".	Literal Number, p. 1-1001

Relationship of the GROUP BY Clause to the SELECT Clause

A GROUP BY clause restricts what you can enter in the SELECT clause. If you use a GROUP BY clause, each column that you select must be in the GROUP BY list. If you use an aggregate function and one or more column expressions 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. Do not put constant expressions or BYTE or TEXT column expressions in the GROUP BY list.

If you are selecting a BYTE or TEXT column, you cannot use the GROUP BY clause. In addition, you cannot use ROWID in a GROUP BY clause.

If your select list includes a column with a user-defined data type, the type must either use the database server's built-in bit-hashing function or have its own hash function. Otherwise, you cannot use a GROUP BY clause.

The following example names one column that is not in an aggregate expression. The total_price column should not be in the GROUP BY list because it appears as the argument of an aggregate function. The COUNT and SUM keywords are applied to each group, not the whole query set.

SELECT order_num, COUNT(*), SUM(total_price)

	FROM items

	GROUP BY order_num

If a column stands alone in a column expression in the select list, you must use it in the GROUP BY clause. If a column is combined with another column by an arithmetic operator, you can choose to group by the individual columns or by the combined expression using a specific number.

Using Select Numbers

You can use one or more integers in the GROUP BY clause to stand for column expressions. In the following example, the first SELECT statement uses select numbers for order_date and paid_date - order_date in the GROUP BY clause. You can group only by a combined expression using the select-number notation. In the second SELECT statement, you cannot replace the 2 with the expression paid_date - order_date.

SELECT order_date, COUNT(*), paid_date - order_date

	FROM orders

	GROUP BY 1, 3



SELECT order_date, paid_date - order_date

	FROM orders

	GROUP BY order_date, 2

Nulls in the GROUP BY Clause

Each row that contains a null value in a column that is specified by a GROUP BY clause belongs to a single group (that is, all null values are grouped together).

HAVING Clause

Use the HAVING clause to apply one or more qualifying conditions to group

In the following examples, each condition compares one calculated property of the group with another calculated property of the group or with a constant. The first SELECT statement uses a HAVING clause that compares the calculated expression COUNT(*) with the constant 2. The query returns the average total price per item on all orders that have more than two items. The second SELECT statement lists customers and the call months if they have made two or more calls in the same month.

SELECT order_num, AVG(total_price) FROM items

	GROUP BY order_num

	HAVING COUNT(*) > 2 



SELECT customer_num, EXTEND (call_dtime, MONTH TO MONTH) 

	FROM cust_calls

	GROUP BY 1, 2

	HAVING COUNT(*) > 1

You can use the HAVING clause to place conditions on the GROUP BY column values as well as on calculated values. The following example returns the customer_num, call_dtime (in full year-to-fraction format), and cust_code, and groups them by call_code for all calls that have been received from customers with customer_num less than 120:

SELECT customer_num, EXTEND (call_dtime), call_code 

	FROM cust_calls

	GROUP BY call_code, 2, 1

	HAVING customer_num < 120

The HAVING clause generally complements a GROUP BY clause. If you use a HAVING clause without a GROUP BY clause, the HAVING clause applies to all rows that satisfy the query. Without a GROUP BY clause, all rows in the table make up a single group. The following example returns the average price of all the values in the table, as long as more than ten rows are in the table:

SELECT AVG(total_price) FROM items

	HAVING COUNT(*) > 10

ORDER BY Clause

Use the ORDER BY clause to sort query results by the values that are contained in one or more columns.

(1 of 2)
Element	Purpose	Restrictions	Syntax
alias	The alias assigned to a table or view in the `FROM` clause. See "FROM Clause" for more information on aliases for tables and views.	You cannot specify an alias for a table or view in the `ORDER BY` clause unless you have assigned the alias to the table or view in the `FROM` clause.	Identifier, p. 1-966
column name	The name of a column in the specified table or view. The query results are sorted by the values contained in this column.	A column specified in the `ORDER BY` clause must be listed explicitly or implicitly in the select list of the `SELECT` clause. If you want to order the query results by a derived column, you must supply a display label for the derived column in the select list and specify this label in the `ORDER BY` clause. Alternatively, you can omit a display label for the derived column in the select list and specify the derived column by means of a select number in the `ORDER BY` clause. This cannot be a column whose data type is a collection.	Identifier, p. 1-966
display label	A temporary name that you assign to a column in the select list of the `SELECT` clause. You can use a display label in place of the column name in the `ORDER BY` clause.	You cannot specify a display label in the `ORDER BY` clause unless you have specified this display label for a column in the select list.	Identifier, p. 1-966
first	The position of the first character in the portion of the column that is used to sort the query results	The column must be one of the following character types: `BYTE`, `CHAR`, `NCHAR`, `NVARCHAR`, `TEXT`, or `VARCHAR`.	Literal Number, p. 1-1001
last	The position of the last character in the portion of the column that is used to sort the query results	The column must be one of the following character types: `BYTE`, `CHAR`, `NCHAR`, `NVARCHAR`, `TEXT`, or `VARCHAR`.	Literal Number, p. 1-1001
select number	An integer that identifies a column in the select list of the `SELECT` clause by specifying its order in the select list. You can use a select number in place of a column name in the `ORDER BY` clause.	You must specify select numbers in the `ORDER BY` clause when `SELECT` statements are joined by `UNION` or `UNION ALL` keywords and compatible columns in the same position have different names.	Literal Number, p. 1-1001

You can perform an ORDER BY operation on a column or on an aggregate expression when you use SELECT * or a display label in your SELECT statement.

The following query explicitly selects the order date and shipping date from the orders table and then rearranges the query by the order date. By default, the query results are listed in ascending order.

SELECT order_date, ship_date FROM orders

	ORDER BY order_date

In the following query, the order_date column is selected implicitly by the SELECT * statement, so you can use order_date in the ORDER BY clause:

SELECT * FROM orders

	ORDER BY order_date

Ordering by a Column Substring

You can order by a column substring instead of ordering by the entire length of the column. The column substring is the portion of the column that the database server uses for the sort. You define the column substring by specifying column subscripts (the first and last parameters). The column subscripts represent the starting and ending character positions of the column substring.

The following example shows a SELECT statement that queries the customer table and specifies a column substring in the ORDER BY column. The column substring instructs the database server to sort the query results by the portion of the lname column contained in the sixth through ninth positions of the column.

SELECT * from customer

	ORDER BY lname[6,9]

Assume that the value of lname in one row of the customer table is Greenburg. Because of the column substring in the ORDER BY clause, the database server determines the sort position of this row by using the value burg, not the value Greenburg.

You can specify column substrings only for columns that have a character data type. If you specify a column substring in the ORDER BY clause, the column must have one of the following data types: BYTE, CHAR, NCHAR, NVARCHAR, TEXT, or VARCHAR.

GLS

For information on the GLS aspects of using column substrings in the ORDER BY clause, see the Guide to GLS Functionality.

Ordering by a Derived Column

You can order by a derived column by supplying a display label in the SELECT clause, as shown in the following example:

SELECT paid_date - ship_date span, customer_num

	FROM orders

	ORDER BY span

Ascending and Descending Orders

You can use the ASC and DESC keywords to specify ascending (smallest value first) or descending (largest value first) order. The default order is ascending.

For DATE and DATETIME data types, smallest means earliest in time and largest means latest in time. For standard character data types, the ASCII collating sequence is used. See page 1-1021 for a listing of the collating sequence.

Nulls in the ORDER BY Clause

Null values are ordered as less than values that are not null. Using the ASC order, the null value comes before the non-null value; using DESC order, the null value comes last.

Nested Ordering

If you list more than one column in the ORDER BY clause, your query is ordered by a nested sort. The first level of sort is based on the first column; the second column determines the second level of sort. The following example of a nested sort selects all the rows in the cust_calls table and orders them by call_code and by call_dtime within call_code:

SELECT * FROM cust_calls

	ORDER BY call_code, call_dtime

Using Select Numbers

In place of column names, you can enter one or more integers that refer to the position of items in the SELECT clause. You can use a select number to order by an expression. For instance, the following example orders by the expression paid_date - order_date and customer_num, using select numbers in a nested sort:

SELECT order_num, customer_num, paid_date - order_date 

	FROM orders

	ORDER BY 3, 2

Select numbers are required in the ORDER BY clause when SELECT statements are joined by the UNION or UNION ALL keywords and compatible columns in the same position have different names.

Ordering by Rowids

You can specify the rowid column as a column in the ORDER BY clause. The rowid column is a hidden column in nonfragmented tables and in fragmented tables that were created with the WITH ROWIDS clause. The rowid column contains a unique internal record number that is associated with a row in a table. Informix recommends, however, that you utilize primary keys as an access method rather than exploiting the rowid column.

If you want to specify the rowid column in the ORDER BY clause, enter the keyword ROWID in lowercase or uppercase letters. You cannot specify the rowid column in the ORDER BY clause:

if the table from which you are selecting is a fragmented table that does not have a rowid column.
unless you have included the rowid column in the select list of the SELECT clause.

For further information on using the rowid column in column expressions, see "Expression".

ORDER BY Clause with DECLARE

ESQL

You cannot use a DECLARE statement with a FOR UPDATE clause to associate a cursor with a SELECT statement that has an ORDER BY clause.

Placing Indexes on ORDER BY Columns

When you include an ORDER BY clause in a SELECT statement, you can improve the performance of the query by creating an index on the column or columns that the ORDER BY clause specifies. The database server uses the index that you placed on the ORDER BY columns to sort the query results in the most efficient manner. For further information on creating indexes that correspond to the columns of an ORDER BY clause, see "ASC and DESC Keywords" under the CREATE INDEX statement.

FOR UPDATE Clause

Use the FOR UPDATE clause when you prepare a SELECT statement, and you intend to update the values returned by the SELECT statement when the values are fetched. Preparing a SELECT statement that contains a FOR UPDATE clause is equivalent to preparing the SELECT statement without the FOR UPDATE clause and then declaring a FOR UPDATE cursor for the prepared statement.

The FOR UPDATE keyword notifies the database server that updating is possible, causing it to use more-stringent locking than it would with a select cursor. You cannot modify data through a cursor without this clause. You can specify particular columns that can be updated.

After you declare a cursor for a SELECT... FOR UPDATE statement, you can update or delete the currently selected row using an UPDATE or DELETE statement with the WHERE CURRENT OF clause. The words CURRENT OF refer to the row that was most recently fetched; they replace the usual test expressions in the WHERE clause.

To update rows with a particular value, your program might contain statements such as the sequence of statements shown in the following example:

EXEC SQL BEGIN DECLARE SECTION;

    char fname[ 16];

    char lname[ 16];

    EXEC SQL END DECLARE SECTION;

.

.

.

EXEC SQL connect to 'stores7';

 /* select statement being prepared contains a for update clause */

EXEC SQL prepare x from 'select fname, lname from customer for update';

EXEC SQL declare xc cursor for x; --note no 'for update' clause in declare


for (;;)

	{

	EXEC SQL fetch xc into $fname, $lname;

	if (strncmp(SQLSTATE, '00', 2) != 0) break;

 	printf("%d %s %s\n",cnum, fname, lname );

	if (cnum == 999)										--update rows with 999 customer_num

 		EXEC SQL update customer set fname = 'rosey' where current of xc;

	}



 EXEC SQL close xc;

 EXEC SQL disconnect current;

A SELECT ... FOR UPDATE statement, like an update cursor, allows you to perform updates that are not possible with the UPDATE statement alone, because both the decision to update and the values of the new data items can be based on the original contents of the row. The UPDATE statement cannot interrogate the table that is being updated.

Syntax That Is Incompatible with the FOR UPDATE Clause

A SELECT statement that uses a FOR UPDATE clause must conform to the following restrictions:

The statement can select data from only one table.
The statement cannot include any aggregate functions.
The statement cannot include any of the following clauses or keywords: DISTINCT, FOR READ ONLY, GROUP BY, INTO TEMP, ORDER BY, UNION, or UNIQUE.

For information on how to declare an update cursor for a SELECT statement that does not include a FOR UPDATE clause, see page 1-303.

FOR READ ONLY Clause

Use the FOR READ ONLY clause to specify that the select cursor declared for the SELECT statement is a read-only cursor. A read-only cursor is a cursor that cannot modify data. This section provides the following information about the FOR READ ONLY clause:

When you must use the FOR READ ONLY clause
Syntax restrictions on a SELECT statement that uses a FOR READ ONLY clause

Using the FOR READ ONLY Clause in Read-Only Mode

Normally, you do not need to include the FOR READ ONLY clause in a SELECT statement. A SELECT statement is a read-only operation by definition, so the FOR READ ONLY clause is usually unnecessary. However, in certain special circumstances, you must include the FOR READ ONLY clause in a SELECT statement.

ANSI

If you have used the High-Performance Loader (HPL) in express mode to load data into the tables of an ANSI-mode database, and you have not yet performed a level-0 backup of this data, the database is in read-only mode. When the database is in read-only mode, the database server rejects any attempts by a select cursor to access the data unless the SELECT or the DECLARE includes a FOR READ ONLY clause. This restriction remains in effect until the user has performed a level-0 backup of the data.

When the database is an ANSI-mode database, select cursors are update cursors by default. An update cursor is a cursor that can be used to modify data. These update cursors are incompatible with the read-only mode of the database. For example, the following SELECT statement against the customer_ansi table fails:

EXEC SQL declare ansi_curs cursor for

	select * from customer_ansi;

The solution is to include the FOR READ ONLY clause in your select cursors. The read-only cursor that this clause specifies is compatible with the read-only mode of the database. For example, the following SELECT FOR READ ONLY statement against the customer_ansi table succeeds:

EXEC SQL declare ansi_read cursor for

	select * from customer_ansi for read only;

DB

DB-Access executes all SELECT statements with select cursors. Therefore, you must include the FOR READ ONLY clause in all SELECT statements that access data in a read-only ANSI-mode database. The FOR READ ONLY clause causes DB-Access to declare the cursor for the SELECT statement as a read-only cursor.

For more information on the express mode of HPL, see the Guide to the High-Performance Loader. For more information on level-0 backups, see the INFORMIX-Universal Server Archive and Backup Guide. For more information on select cursors, read-only cursors, and update cursors, see the DECLARE statement on page 1-303.

Syntax That Is Incompatible with the FOR READ ONLY Clause

Whether your database is an ANSI-mode database or a database that is not ANSI compliant, you cannot include both the FOR READ ONLY clause and the FOR UPDATE clause in the same SELECT statement. If you attempt to do so, the SELECT statement fails.

For information on how to declare a read-only cursor for a SELECT statement that does not include a FOR READ ONLY clause, see page 1-303.

INTO TEMP Clause

Element Purpose Restrictions Syntax
temp table name
The simple name of a temporary table. This table contains the results of the `SELECT` statement. The column names of the temporary table are those that are named in the select list of the `SELECT` clause.
The name must be different from any existing table, view, or synonym name in the current database, but it does not have to be different from other temporary table names used by other users. You must have the Connect privilege on a database to create a temporary table in that database. If you use the `INTO TEMP` clause to create a temporary table, you must supply a display label for all expressions in the select list other than simple column expressions.
Identifier, p. 1-966

The INTO TEMP clause creates a temporary table that contains the query results. The initial and next extents for the temporary table are always eight pages. The temporary table must be accessible by the database server's built-in RSAM access method; you cannot specify an alternate access method.

Temporary tables created with the INTO TEMP clause are explicit temporary tables. Explicit temporary tables can also be created with the CREATE TEMP TABLE statement.

If the DBSPACETEMP environment variable is set for INFORMIX-Universal Server, temporary tables created with the INTO TEMP clause are located in the dbspaces that are specified in the DBSPACETEMP list. You can also specify dbspace settings with the ONCONFIG parameter DBSPACETEMP. If neither the environment variable nor configuration parameter is set, the default setting is the root dbspace. The settings specified for the DBSPACETEMP environment variable take precedence over the ONCONFIG parameter DBSPACETEMP and the default setting. For more information about creating temporary tables, see the CREATE TABLE statement on page 1-211. For more information about the DBSPACETEMP environment variable, see Chapter 3 of the Informix Guide to SQL: Reference. For more information about the ONCONFIG parameter DBSPACETEMP, see the INFORMIX-Universal Server Administrator's Guide.

If a temporary table is created with logging, it does not disappear automatically when your program ends; you must issue a DROP TABLE statement on the temporary table. If your database does not have logging, or if it has logging, and you created the temporary table without the WITH NO LOG keywords, the temporary table disappears when you close the current database.

If you use the same query results more than once, using a temporary table saves time. In addition, using an INTO TEMP clause often gives you clearer and more understandable SELECT statements. However, the data in the temporary table is static; data is not updated as changes are made to the tables used to build the temporary table.

The column names of the temporary table are those named in the SELECT clause. You must supply a display label for all expressions other than simple column expressions. The display label for a column or expression becomes the column name in the temporary table. If you do not provide a display label for a column expression, the temporary table uses the column name from the select list. The following example creates the pushdate table with two columns, customer_num and slowdate:

SELECT customer_num, call_dtime + 5 UNITS DAY slowdate

	FROM cust_calls INTO TEMP pushdate

You can put indexes on a temporary table.

INTO TEMP Clause and WHERE Clause

When you use the INTO TEMP clause combined with the WHERE clause, and no rows are returned, the SQLNOTFOUND value is 100 in ANSI-compliant databases and 0 in databases that are not ANSI compliant. If the SELECT INTO
TEMP...WHERE... statement is a part of a multistatement prepare and no rows are returned, the SQLNOTFOUND value is 100 for both ANSI-compliant databases and databases that are not ANSI compliant.

INTO TEMP Clause and INTO

ESQL

Do not use the INTO option with the INTO TEMP clause. If you do, no results are returned to the program variables and the SQLCODE (sqlca.sqlcode) variable is set to a negative value.

WITH NO LOG Option

If you use the WITH NO LOG keywords, operations on the temporary table are not included in the transaction-log operations. You can use this option to reduce the overhead of transaction logging.

UNION Operator

Place the UNION operator between two SELECT statements to combine the queries into a single query. You can string several SELECT statements together using the UNION operator. Corresponding items do not need to have the same name.

Restrictions on Combined SELECT

Several restrictions apply on the queries that you can connect with a UNION operator, as the following list describes:

The number of items in the SELECT clause of each query must be the same, and the corresponding items in each SELECT clause must have compatible data types.
If you use an ORDER BY clause, it must follow the last SELECT clause, and you must refer to the item ordered by integer, not by identifier. Ordering takes place after the set operation is complete.
You cannot use a UNION operator inside a subquery or in the definition of a view.

ESQL
You cannot use an INTO clause in a query unless you are sure that the compound query returns exactly one row, and you are not using a cursor. In this case, the INTO clause must be in the first SELECT statement.

To put the results of a UNION operator into a temporary table, use an INTO TEMP clause in the final SELECT statement.

Duplicate Rows in a Combined SELECT

If you use the UNION operator alone, the duplicate rows are removed from the complete set of rows. That is, if multiple rows contain identical values in each column, only one row is retained. If you use the UNION ALL operator, all the selected rows are returned (the duplicates are not removed). The following example uses the UNION ALL operator to join two SELECT statements without removing duplicates. The query returns a list of all the calls that were received during the first quarter of 1993 and the first quarter of 1994.

SELECT customer_num, call_code FROM cust_calls

	WHERE call_dtime BETWEEN 

	 	DATETIME (1993-1-1) YEAR TO DAY

	 	AND DATETIME (1993-3-31) YEAR TO DAY



UNION ALL



SELECT customer_num, call_code FROM cust_calls

	WHERE call_dtime BETWEEN 

	 	DATETIME (1994-1-1)YEAR TO DAY

	 	AND DATETIME (1994-3-31) YEAR TO DAY

If you want to remove duplicates, use the UNION operator without the keyword ALL in the query. In the preceding example, if the combination 101 B were returned in both SELECT statements, a UNION operator would cause the combination to be listed once. (If you want to remove duplicates within each SELECT statement, use the DISTINCT keyword in the SELECT clause, as described on page 1-601.)

References

In this manual, see the descriptions of the DECLARE and FOREACH statements.

In the Informix Guide to SQL: Tutorial, see the discussion of the SELECT statement in Chapter 2 and Chapter 3, and Chapter 14 for the discussion of SPL routines. In the Guide to GLS Functionality, see the discussion of the GLS aspects of the SELECT statement.

For information on how to access row and collections with ESQL/C host variables, see the discussion of complex data types in the INFORMIX-ESQL/C Programmer's Manual.

Informix Guide to SQL: SyntaxChapter 1: SQL Statements Home Contents Index Master Index New Book

SQL Statements

SELECT

Expressions in the Select List

Selecting Constants

Selecting Function Expressions

Selecting Aggregate Expressions

Selecting SPL Function Expressions

Selecting Expressions That Use Arithmetic Operators

Selecting Row Fields

INTO Clause

INTO Clause with Indicator Variables

Using the INTO clause in the FETCH statement

AS Keyword with Aliases

BETWEEN Condition

IN Condition

IS NULL Condition

LIKE or MATCHES Condition

IN Subquery

EXISTS Subquery

Using a Join in the WHERE Clause

Multiple-Table Joins

Self-Joins

Outer Joins

Nulls in the GROUP BY Clause

ORDER BY Clause

Ordering by a Column Substring

Ascending and Descending Orders

Using Select Numbers

Using the FOR READ ONLY Clause in Read-Only Mode

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book