SQL Statements
Argument
Use an Argument to pass a specific value to a routine parameter.
Syntax
Usage
A parameter list for a routine is defined in the CREATE PROCEDURE or CREATE FUNCTION statement. If the routine has a parameter list, you can enter arguments when you execute the routine. An argument is a specific data element that matches the data type of one of the parameters for the routine.
When you execute a routine, you can enter arguments in one of two ways:
If you use a parameter name for one argument, you must use a parameter name for all the arguments.
In the following example, both statements are valid for a function that expects three character arguments, t, d, and n:
When you use the parameter name in the argument list (called passing parameters by name), the process of routine resolution is partial, based only on the routine type (FUNCTION or PROCEDURE), the routine name, and the number of arguments.
Comparing Arguments to the Parameter List
When you create or register a routine with CREATE PROCEDURE or CREATE FUNCTION, you specify a parameter list with the names and data types of the parameters the routine expects.
If you attempt to execute a routine with more arguments than the routine expects, you receive an error.
If you execute a routine with fewer arguments than the routine expects, the arguments are said to be missing. The database server initializes missing arguments to their corresponding default values. This initialization occurs before the first executable statement in the body of the routine.
If missing arguments do not have default values, the database server initializes the arguments to the value UNDEFINED. However, you cannot use a variable with a value of UNDEFINED within the routine. If you do, INFORMIX-Universal Server issues an error.
Subset of SELECT Allowed in a Routine Argument
You can use any SELECT statement as the argument for a routine if it returns exactly one value of the proper data type and length. For more information, see the discussion of the SELECT statement on page 1-596.
Subset of Expressions Allowed as an Argument
You can use any expression as an argument, except an aggregate expression. If you use a subquery or function call, the subquery or function must return a single value of the appropriate data type and size. For the full syntax of an expression, see page 1-880.
References
In this manual, see the CREATE FUNCTION, CREATE PROCEDURE, EXECUTE FUNCTION, EXECUTE PROCEDURE, CALL, FOREACH, and LET statements. See also the Parameter List segment.
For information about how to write external routines, see the Extending INFORMIX-Universal Server: User-Defined Routines manual. In the Informix Guide to SQL: Tutorial, see Chapter 14 for information about how to write SPL routines.
Collection Derived Table
The Collection Derived Table segment specifies a collection or row variable to access instead of a table name.
Syntax
Usage
The TABLE keyword introduces the name of the collection or row variable that you want to access as a collection derived table. The variable can be typed or untyped. For example, the following INSERT statement uses the variable in its Collection Derived Table clause:
In an ESQL/C program, variable is a host variable for either a collection or a row and is declared as either the collection or row data type. 
In an SPL program, variable is an SPL variable that is declared as a COLLECTION, MULTISET, SET, or LIST data type.
When you use the Collection Derived Table segment, you access the elements of a collection or the fields of a row variable. Use of this segment does not affect the associated column or columns in a database. Once you have completed the modifications to the variable, save the new values in the database with the UPDATE or INSERT statement.
Accessing a Collection Variable
The TABLE keyword makes the collection variable a collection derived table, that is, a collection appears as a table in an SQL statement. You can think of a collection derived table as a table of one column, with each element of the collection being a row of the table.
Use the TABLE keyword in place of the name of a table, synonym, or view name in the following SQL statements:
The following ESQL/C code fragment inserts the element 3 into the a_list collection variable and then saves this collection variable in the list_col column of the tab_list table:
If the ESQL/C collection variable is an untyped collection variable, you must perform a SELECT from the collection column before you use the variable in the Collection Derived Table segment. The SELECT statement allows the database server to obtain the collection type. Suppose the a_list host variable is declared as an untyped collection variable, as follows:
The following code fragment obtains the collection type for the a_list variable before it uses the collection variable in an UPDATE statement:
The following SPL code fragment inserts the element 3 into the a_list collection variable and then saves this collection variable in the list_col column of the tab_list table:
Accessing a Row Variable
The TABLE keyword can make an ESQL/C row variable a collection derived table, that is, a row appears as a table in an SQL statement. For a row variable, you can think of the collection derived table as a table of one row, with each field of the row type being a column of the table row.
Use the TABLE keyword in place of the name of a table, synonym, or view name in the following SQL statements:
The DELETE and INSERT statements do not support a row variable in the Collection Derived Table segment.
For example, suppose an ESQL/C host variable a_row has the following declaration:
The following ESQL/C code fragment adds the fields in the a_row variable to the row_col column of the tab_row table:
References
See the DECLARE, DELETE, FETCH, INSERT, SELECT, and UPDATE statements in Chapter 1 of this manual for further information about how to access collection variables.
For information on how to use collection variables in an SPL routine, see Chapter 14 in the Informix Guide to SQL: Tutorial. For information on how to use collection or row variables in an ESQL/C program, see the chapter on complex data types in the INFORMIX-ESQL/C Programmer's Manual.
Condition
A condition tests data to determine whether it meets certain qualifications. Use the Condition segment wherever you see a reference to a condition in a syntax diagram.
Syntax
Usage
A condition is a collection of one or more search conditions, optionally connected by the logical operators AND or OR. Search conditions fall into the following categories:
Restrictions on a Condition
A condition can contain only an aggregate function if it is used in the HAVING clause of a SELECT statement or the HAVING clause of a subquery. You cannot use an aggregate function in a comparison condition that is part of a WHERE clause in a DELETE, SELECT, or UPDATE statement unless 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.
NOT Operator
If you preface a condition with the keyword NOT, the test is true only if the condition that NOT qualifies is false. If the condition that NOT qualifies is unknown (uses a null in the determination), the NOT operator has no effect. The following truth table shows the effect of NOT. The letter T represents a true condition, F represents a false condition, and a question mark (?) represents an unknown condition.Unknown values occur when part of an expression that uses an arithmetic operator is null.
Comparison Conditions (Boolean Expressions)
Five kinds of comparison conditions exist: Relational Operator, BETWEEN, IN, IS NULL, and LIKE and MATCHES. Comparison conditions are often called Boolean expressions because they evaluate to a simple true or false result. Their syntax is summarized in the following diagram and explained in detail after the diagram.
Refer to the following sections for more information on the use of the different types of comparison conditions:
Quotation Marks in Conditions
When you compare a column expression with a constant expression in any type of comparison condition, observe the following rules:
The following example shows the correct use of quotation marks in comparison conditions. The ship_instruct column has a character data type. The order_date column has a date data type. The ship_weight column has a numeric data type.
Relational-Operator Condition
Some relational-operator conditions are shown in the following examples:
If either expression is null for a row, the condition evaluates to false. For example, if paid_date has a null value, you cannot use either of the following statements to retrieve that row:
An IS NULL condition finds a null value, as shown in the following example. The IS NULL condition is explained fully in "IS NULL Condition" on page 1-844.
For more information, see the Relational Operator segment on page 1-1018.
BETWEEN Condition
For a BETWEEN test to be true, the value of the expression on the left of the BETWEEN keyword must be in the inclusive range of the values of the two expressions on the right of the BETWEEN keyword. Null values do not satisfy the condition. You cannot use NULL for either expression that defines the range.
Some BETWEEN conditions are shown in the following examples:
IN Condition
The IN condition is satisfied when the expression to the left of the word IN is included in the list of items.
The NOT option produces a search condition that is satisfied when the expression is not in the list of items. Null values do not satisfy the condition.
The following examples show some IN conditions:
The TODAY function is evaluated at execution time; CURRENT is evaluated when a cursor opens or when the query executes, if it is a singleton SELECT statement. 
The USER function is case sensitive; it perceives minnie and Minnie as different values.
Using the IN Operator with Collection Data Types
You can use the IN operator to determine if an element is contained in a collection. The collection you search can be a simple or nested collection. In a nested collection type, the element type of the collection is also a collection type.
When you use the IN operator to search for an element in a collection the expression to the left or right of the IN keyword cannot contain a BYTE or TEXT data type.
Suppose you create the following table that contains two collection columns:
The following partial examples show how you might use the IN operator for search conditions on the collection columns of the tab_coll table:
In general, when you use the IN operator on a collection data type, the database server checks whether the value on the left hand side of the of the IN operator is an element in the set of values on the right hand side.
IS NULL Condition
The IS NULL condition is satisfied if the column contains a null value. If you use the IS NOT NULL option, the condition is satisfied when the column contains a value that is not null. The following example shows an IS NULL condition:
LIKE and MATCHES Condition
A LIKE or MATCHES condition tests for matching character strings. The condition is true, or satisfied, when either of the following tests is true:
You can use the single quote (') only with the quoted string to match a literal quote; you cannot use the ESCAPE keyword. You can use the quote character as the escape character in matching any other pattern if you write it as ''''.
NOT Operator
The NOT operator makes the search condition successful when the column on the left has a value that is not null and does not match the pattern that the quoted string specifies. For example, the following conditions exclude all rows that begin with the characters Baxter in the lname column:
LIKE Operator
If you use the keyword LIKE, you can use the following wildcard characters in the quoted string.
Using the backslash (\) as an escape character is an Informix extension to ANSI-compliant SQL.
If you use an escape character to escape anything other than percent sign (%), underscore (_), or the escape character itself, an error is returned.
The following condition tests for the string tennis, alone or in a longer string, such as tennis ball or table tennis paddle:
The following condition tests for all descriptions that contain an underscore. The backslash (\) is necessary because the underscore (_) is a wildcard character.
The LIKE operator has an associated operator function called like(). You can define a like() function to handle your own user-defined data types. For more information, see the Extending INFORMIX-Universal Server: Data Types manual.
MATCHES Operator
If you use the keyword MATCHES, you can use the following wildcard characters in the quoted string.
The following condition tests for the string tennis, alone or in a longer string, such as tennis ball or table tennis paddle:
The following condition is true for the names Frank and frank:
The following condition is true for any name that begins with either F or f:
The MATCHES operator has an associated operator function called matches(). You can define a matches() function to handle your own user-defined data types. For more information, see the Extending INFORMIX-Universal Server: Data Types manual.
ESCAPE with LIKE
The ESCAPE keyword lets you include an underscore (_) or a percent sign (%) in the quoted string and avoid having them be interpreted as wildcards. If you choose to use z as the escape character, the characters z_ in a string stand for the character _. Similarly, the characters z% represent the percent sign (%). Finally, the characters zz in the string stand for the single character z. The following statement retrieves rows from the customer table in which the company column includes the underscore character:
You can also use a single-character host variable as an escape character. The following statement shows the use of a host variable as an escape character:
ESCAPE with MATCHES
The ESCAPE clause lets you include a question mark (?), an asterisk (*), and a left or right bracket ([]) in the quoted string and avoid having them be interpreted as wildcards. If you choose to use z as the escape character, the characters z? in a string stand for the question mark (?). Similarly, the characters z* stand for the asterisk (*). Finally, the characters zz in the string stand for the single character z.
The following example retrieves rows from the customer table in which the value of the company column includes the question mark (?):
Stand-Alone Condition
A stand-alone condition can be any expression that is not explicitly listed in the syntax for the comparison condition. Such an expression is valid only if its result is of the Boolean type. For example, the following example returns a value of the Boolean type:
Condition with a Subquery
You can use a SELECT statement within a condition; this combination is called a subquery. You can use a subquery in a SELECT statement to perform the following functions:
The subquery can depend on the current row that the outer SELECT statement is evaluating; in this case, the subquery is a correlated subquery.
The kinds of subquery conditions are shown in the following sections with their syntax.
A subquery can return a single value, no value, or a set of values depending on the context in which it is used. If a subquery returns a value, it must select only a single column. If the subquery simply checks whether a row (or rows) exists, it can select any number of rows and columns. A subquery cannot contain an ORDER BY clause. The full syntax of the SELECT statement is described on page 1-596.
IN Subquery
An IN subquery condition is true if the value of the expression matches one or more of the values that the subquery selects. The subquery must return only one column, but it can return more than one row. The keyword IN is equivalent to the =ANY sequence. The keywords NOT IN are equivalent to the !=ALL sequence. See "ALL/ANY/SOME Subquery".
The following example of an IN subquery finds the order numbers for orders that do not include baseball gloves (stock_num = 1):
Because the IN subquery tests for the presence of rows, duplicate rows in the subquery results do not affect the results of the main query. Therefore, you can put the UNIQUE or DISTINCT keyword into the subquery with no effect on the query results, although eliminating testing duplicates can reduce the time needed for running the query.
EXISTS Subquery
An EXISTS subquery condition evaluates to true if the subquery returns a row. With an EXISTS subquery, one or more columns can be returned. The subquery always contains a reference to a column of the table in the main query. If you use an aggregate function in an EXISTS subquery, at least one row is always 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). You can appropriately use an EXISTS subquery in this SELECT statement because you use the subquery to test both stock_num and manu_code in items.
The preceding example works equally well if you use SELECT * in the subquery in place of the column names because the existence of the whole row is tested; specific column values are not tested.
ALL/ANY/SOME Subquery
You use the ALL, ANY, and SOME keywords to specify what makes the search condition true or false. A search condition that is true when the ANY keyword is used might not be true when the ALL keyword is used, and vice versa.
In the following example of the ALL subquery, the first condition tests whether each total_price is greater than the total price of every item in order number 1023. The second condition uses the MAX aggregate function to produce the same results.
The following conditions are true when the total price is greater than the total price of at least one of the items in order number 1023. The first condition uses the ANY keyword; the second uses the MIN aggregate function.
Using the NOT keyword with an ANY subquery tests whether an expression is not true for any subquery value. The condition, which is found in the following example of the NOT keyword with an ANY subquery, is true when the expression total_price is not greater than any selected value. That is, it is true when total_price is greater than none of the total prices in order number 1023.
Omitting ANY, ALL, or SOME Keywords
You can omit the keywords ANY, ALL, or SOME in a subquery if you know that the subquery will return exactly one value. If you omit the ANY, ALL, or SOME keywords, 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:
Conditions with AND or OR Operators
You can combine simple conditions with the logical operators AND or OR to form complex conditions. The following SELECT statements contain examples of complex conditions in their WHERE clauses:
The following truth tables show the effect of the AND and OR operators.The letter T represents a true condition, F represents a false condition, and the question mark (?) represents an unknown value. Unknown values occur when part of an expression that uses a logical operator is null.
If the Boolean expression evaluates to UNKNOWN, the condition is not satisfied.
Consider the following example within a WHERE clause:
The row where order_num = 1023 is the row where ship_weight is null. Because ship_weight is null, ship_charge/ship_weight is also null; therefore, the truth value of ship_charge/ship_weight < 5 is UNKNOWN. Because order_num = 1023 is TRUE, the AND table states that the truth value of the entire condition is UNKNOWN. Consequently, that row is not chosen. If the condition used an OR in place of the AND, the condition would be true.
References
In the Informix Guide to SQL: Tutorial, see the discussion of conditions in the SELECT statement in Chapter 2 and Chapter 3.
In the Guide to GLS Functionality, see the discussion of the SELECT statement for information on the GLS aspects of conditions.
|
