Informix Guide to SQL: Tutorial Composing SELECT Statements

You can query a single table in a database in many ways. You can tailor a SELECT statement to perform the following actions:

• Retrieve all or specific columns
• Retrieve all or specific rows
• Perform computations or other functions on the retrieved data
• Order the data in various ways

The most basic SELECT statement contains only the two required clauses, SELECT and FROM.

Query 2-5a specifies all the columns in the manufact table in a select list. A select list is a list of the column names or expressions that you want to project from a table.

SELECT  manu_code,  manu_name,  lead_time

	FROM  manufact

Query 2-5b uses the wildcard asterisk symbol (*) as shorthand in the select list to represent the names of all the columns in the table. You can use the asterisk symbol (*) when you want all the columns in their defined order.

SELECT  *  FROM  manufact

Query 2-5a and Query 2-5b are equivalent and display the same results; that is, a list of every column and row in the manufact table. Query Result 2-5 shows the results as they would appear on a DB-Access or a Relational Object Manager screen.

Query Result 2-5

Query 2-6 shows how you can change the order in which the columns are listed by changing their order in your select list.

SELECT  manu_name,  manu_code,  lead_time

	FROM  manufact 

Query Result 2-6 includes the same columns as the previous query result, but because the columns are specified in a different order, the display is also different.

Query Result 2-6

You can add an ORDER BY clause to your SELECT statement to direct the system to sort the data in a specific order. You must include the columns that you want to use in the ORDER BY clause in the select list either explicitly or implicitly.

Query 2-7a shows an explicit select list, which includes all the column names that you want to retrieve.

SELECT  manu_code,  manu_name,  lead_time

	FROM  manufact

	ORDER  BY  lead_time  

An implicit select list uses the asterisk symbol (*), as Query 2-7b shows.

SELECT  *  FROM  manufact

	ORDER  BY  lead_time

Query 2-7a and Query 2-7b produce the same display. Query Result 2-7 shows a list of every column and row in the manufact table, in order of
lead_time.

Query Result 2-7

The retrieved data is sorted and displayed, by default, in ascending order. Ascending order is uppercase A to lowercase z for character data types, and lowest to highest value for numeric data types. DATE and DATETIME data is sorted from earliest to latest, and INTERVAL data is ordered from shortest to longest span of time.

Descending order is the opposite of ascending order, from lowercase z to uppercase A for character types and highest to lowest for numeric data types. DATE and DATETIME data is sorted from latest to earliest, and INTERVAL data is ordered from longest to shortest span of time. Query 2-8 shows an example of descending order.

SELECT  *  FROM  manufact

	ORDER  BY  lead_time  DESC 

The keyword DESC following a column name causes the retrieved data to be sorted in descending order, as Query Result 2-8 shows.

Query Result 2-8

You can specify any column (except TEXT, BYTE, BLOB, or CLOB) in the ORDER BY clause, and the database server sorts the data based on the values in that column.

You can also ORDER BY two or more columns, which creates a nested sort. The default is still ascending, and the column that is listed first in the ORDER BY clause takes precedence.

Query 2-9 and Query 2-10 and corresponding query results show nested sorts. To modify the order in which selected data is displayed, change the order of the two columns that are named in the ORDER BY clause.

SELECT  *  FROM  stock

	ORDER  BY  manu_code,  unit_price

In Query Result 2-9, the manu_code column data appears in alphabetical order and, within each set of rows with the same manu_code (for example, ANZ, HRO), the unit_price is listed in ascending order.

Query Result 2-9

Query 2-10 shows the reverse order of the columns in the ORDER BY clause.

SELECT  *  FROM  stock

	ORDER  BY  unit_price,  manu_code

In Query Result 2-10, the data appears in ascending order of unit_price and, where two or more rows have the same unit_price (for example, $20.00, $48.00, $312.00), the manu_code is in alphabetical order.

Query Result 2-10

The order of the columns in the ORDER BY clause is important, and so is the position of the DESC keyword. Although the statements in Query 2-11 contain the same components in the ORDER BY clause, each produces a different result (not shown).

SELECT  *  FROM  stock

	ORDER  BY  manu_code,  unit_price  DESC



SELECT  *  FROM  stock

	ORDER  BY  unit_price,  manu_code  DESC



SELECT  *  FROM  stock

	ORDER  BY  manu_code  DESC,  unit_price  



SELECT  *  FROM  stock

	ORDER  BY  unit_price  DESC,  manu_code  

The previous section shows how to select and order all data from a table. However, often all you want to see is the data in one or more specific columns. Again, the formula is to use the SELECT and FROM clauses, specify the columns and table, and perhaps order the data in ascending or descending order with an ORDER BY clause.

If you want to find all the customer numbers in the orders table, use a statement such as the one in Query 2-12.

SELECT  customer_num  FROM  orders 

Query Result 2-12 shows how the statement simply selects all data in the customer_num column in the orders table and lists the customer numbers on all the orders, including duplicates.

Query Result 2-12

The output includes several duplicates because some customers have placed more than one order. Sometimes you want to see duplicate rows in a projection. At other times, you want to see only the distinct values, not how often each value appears.

To suppress duplicate rows, you can include the keyword DISTINCT or its synonym UNIQUE at the start of the select list, once in each level of a query, as Query 2-13 shows.

SELECT  DISTINCT  customer_num  FROM  orders



SELECT  UNIQUE  customer_num  FROM  orders

To produce a more readable list, Query 2-13 limits the display to show each customer number in the orders table only once, as Query Result 2-13 shows.

Query Result 2-13

Suppose you are handling a customer call, and you want to locate purchase order number DM354331. To list all the purchase order numbers in the orders table, use a statement such as the one that Query 2-14 shows.

SELECT  po_num  FROM  orders

Query Result 2-14 shows how the statement retrieves data in the po_num column in the orders table.

Query Result 2-14

However, the list is not in a useful order. You can add an ORDER BY clause to sort the column data in ascending order and make it easier to find that particular po_num, as Query Result 2-15 shows.

SELECT  po_num  FROM  orders

	ORDER  BY  po_num


 






 Query Result 2-15 







 

To select multiple columns from a table, list them in the select list in the SELECT clause. Query 2-16 shows that the order in which the columns are selected is the order in which they are produced, from left to right.

SELECT  paid_date,  ship_date,  order_date,  

	   customer_num,  order_num,  po_num

	FROM  orders

	ORDER  BY  paid_date,  order_date

As Sorting on Multiple Columns shows, you can use the ORDER BY clause to sort the data in ascending or descending order and perform nested sorts. Query Result 2-16 shows ascending order.

Query Result 2-16

When you use SELECT and ORDER BY on several columns in a table, you might find it helpful to use integers to refer to the position of the columns in the ORDER BY clause. The statements in Query 2-17 retrieve and display the same data, as Query Result 2-17 shows.

SELECT  customer_num,  order_num,  po_num,  order_date

	FROM  orders

	ORDER  BY  4,  1



SELECT  customer_num,  order_num,  po_num,  order_date

	FROM  orders

	ORDER  BY  order_date,  customer_num


 






 Query Result 2-17 







 

You can include the DESC keyword in the ORDER BY clause when you assign integers to column names, as Query 2-18 shows.

SELECT  customer_num,  order_num,  po_num,  order_date

	FROM  orders

	ORDER  BY  4  DESC,  1

In this case, data is first sorted in descending order by order_date and in ascending order by customer_num.

To select part of the value of a character column, include a substring in the select list. Suppose your marketing department is planning a mailing to your customers and wants their geographical distribution based on zip codes. You could write a query similar to the one that Query 2-19 shows.

SELECT  zipcode[1,3],  customer_num  

	FROM  customer

	ORDER  BY  zipcode

Query 2-19 uses a substring to select the first three characters of the zipcode column (which identify the state) and the full customer_num, and lists them in ascending order by zip code, as Query Result 2-19 shows.

Query Result 2-19

By default, Informix database servers use the U.S. English language environment, called a locale, for database data. The U.S. English locale specifies data sorted in code-set order. This default locale uses the ISO 8859-1 code set.

If your database contains non-English data, you should store non-English data in NCHAR (or NVARCHAR) columns to obtain results sorted by the language. The ORDER BY clause should return data in the order appropriate to that language. Query 2-20 uses a SELECT statement with an ORDER BY clause to search the table, abonnés, and to order the selected information by the data in the nom column.

SELECT numéro,nom,prénom 

	FROM abonnés

	ORDER BY nom

The collation order for the results of this query can vary, depending on the following system variations:

• Whether the nom column is CHAR or NCHAR data type. The database server sorts data in CHAR columns by the order the characters appear in the code set. The database server sorts data in NCHAR columns by the order the characters are listed in the collation portion of the locale.
• Whether the database server is using the correct non-English locale when it accesses the database. To use a non-English locale, you must set the CLIENT_LOCALE and DB_LOCALE environment variables to the appropriate locale name.

For Query 2-20 to return expected results, the nom column should be NCHAR data type in a database that uses a French locale. Other operations, such as less than, greater than, or equal to, are also affected by the user-specified locale. For more information on non-English data and locales, see the Informix Guide to GLS Functionality.

Query Result 2-20a and Query Result 2-20b show two sample sets of output.

Query Result 2-20a

Query Result 2-20a follows the ISO 8859-1 code-set order, which ranks uppercase letters before lowercase letters and moves names that contain an accented character (Ålesund, Étaix, Ötker, and Øverst) to the end of the list.

Query Result 2-20b

Query Result 2-20b shows that when the appropriate locale file is referenced by the database server, names including non-English characters (Ålesund, Étaix, Ötker, and Øverst) are collated differently than they are in the ISO 8859-1 code set. They are sorted correctly for the locale. It does not distinguish between uppercase and lowercase letters.

The set of rows that a SELECT statement returns is its active set. A singleton SELECT statement returns a single row. You can add a WHERE clause to a SELECT statement if you want to see only specific rows. For example, you use a WHERE clause to restrict the rows that the database server returns to only the orders that a particular customer placed or the calls that a particular customer service representative entered.

You can use the WHERE clause to set up a comparison condition or a join condition. This section demonstrates only the first use. Join conditions are described in a later section and in the next chapter.

The WHERE clause of a SELECT statement specifies the rows that you want to see. A comparison condition employs specific keywords and operators to define the search criteria.

For example, you might use one of the keywords BETWEEN, IN, LIKE, or MATCHES to test for equality, or the keywords IS NULL to test for null values. You can combine the keyword NOT with any of these keywords to specify the opposite condition.

The following table lists the relational operators that you can use in a WHERE clause in place of a keyword to test for equality.

For CHAR expressions, greater than means after in ASCII collating order, where lowercase letters are after uppercase letters, and both are after numerals. See the ASCII Character Set chart in the Informix Guide to SQL: Syntax. For DATE and DATETIME expressions, greater than means later in time, and for INTERVAL expressions, it means of longer duration.

Important: You cannot use TEXT or BYTE columns to create a comparison condition, except when you use the IS NULL or IS NOT NULL keywords to test for null values.

You cannot specify BLOB or CLOB columns to create a comparison condition on Dynamic Server, except when you use the IS NULL or IS NOT NULL keywords to test for null values.

You can use the preceding keywords and operators in a WHERE clause to create comparison-condition queries that perform the following actions:

• Include values
• Exclude values
• Find a range of values
• Find a subset of values
• Identify null values

To perform variable text searches using the following criteria, use the preceding keywords and operators in a WHERE clause to create comparison-condition queries:

• Exact-text comparison
• Single-character wildcards
• Restricted single-character wildcards
• Variable-length wildcards
• Subscripting

The following section contains examples that illustrate these types of queries.

Use the equal sign (=) relational operator to include rows in a WHERE clause, as Query 2-21 shows.

SELECT  customer_num,  call_code,  call_dtime,  res_dtime

	FROM  cust_calls

	WHERE  user_id  =  'maryj'  

Query 2-21 returns the set of rows that Query Result 2-21 shows.

Query Result 2-21

Use the relational operators != or <> to exclude rows in a WHERE clause.

Query 2-22 assumes that you are selecting from an ANSI-compliant database; the statements specify the owner or login name of the creator of the customer table. This qualifier is not required when the creator of the table is the current user, or when the database is not ANSI compliant. However, you can include the qualifier in either case. For a detailed discussion of owner naming, see the Informix Guide to SQL: Syntax.

SELECT  customer_num,  company,  city,  state

	FROM  odin.customer

	WHERE  state  !=  'CA'



SELECT  customer_num,  company,  city,  state

	FROM  odin.customer

	WHERE  state  <>  'CA'

Both statements in Query 2-22 exclude values by specifying that, in the customer table that the user odin owns, the value in the state column should not be equal to CA, as Query Result 2-22

Query Result 2-22

Query 2-23 shows two ways to specify a range of rows in a WHERE clause.

SELECT  catalog_num,  stock_num,  manu_code,  cat_advert

	FROM  catalog

	WHERE  catalog_num  BETWEEN  10005  AND  10008



SELECT  catalog_num,  stock_num,  manu_code,  cat_advert

	FROM  catalog

	WHERE  catalog_num  >=  10005  AND  catalog_num  <=  10008

Each statement in Query 2-23 specifies a range for catalog_num from 10005 through 10008, inclusive. The first statement uses keywords, and the second statement uses relational operators to retrieve the rows as Query Result 2-23 shows.

Query Result 2-23

Although the catalog table includes a column with the BYTE data type, that column is not included in this SELECT statement because the output would show only the words <BYTE value> by the column name. You can write an SQL API application to display TEXT and BYTE values.

Query 2-24 uses the keywords NOT BETWEEN to exclude rows that have the character range 94000 through 94999 in the zipcode column, as Query Result 2-24 shows.

SELECT  fname,  lname,  company,  city,  state

	FROM  customer

	WHERE  zipcode  NOT  BETWEEN  '94000'  AND  '94999'

	ORDER  BY  state

 






 Query Result 2-24 







 

As Excluding Rows shows, Query 2-25 also assumes the use of an ANSI-compliant database. The owner qualifier is in quotation marks to preserve the case sensitivity of the literal string.

SELECT  lname,  city,  state,  phone

	FROM  'Aleta'.customer

	WHERE  state  =  'AZ'  OR  state  =  'NJ'

	ORDER  BY  lname



SELECT  lname,  city,  state,  phone

	FROM  'Aleta'.customer

	WHERE  state  IN  ('AZ',  'NJ')

	ORDER  BY  lname

Each statement in Query 2-25 retrieves rows that include the subset of AZ or NJ in the state column of the Aleta.customer table, as Query Result 2-25 shows.

Query Result 2-25

Important: You cannot test TEXT or BYTE columns with the IN keyword.

Also, when you use Dynamic Server, you cannot test BLOB or CLOB columns with the IN keyword.

In Query 2-26, an example of a query on an ANSI-compliant database, no quotation marks exist around the table owner name. Whereas the two statements in Query 2-25 searched the Aleta.customer table, Query 2-26 searches the table ALETA.customer, which is a different table, because of the way ANSI-compliant databases look at owner names.

SELECT  lname,  city,  state,  phone

	FROM  Aleta.customer

	WHERE  state  NOT  IN  ('AZ',  'NJ')

	ORDER  BY  state 

Query 2-26 adds the keywords NOT IN, so the subset changes to exclude the subsets AZ and NJ in the state column. Query Result 2-26 shows the results in order of the state column.

Query Result 2-26

Use the IS NULL or IS NOT NULL option to check for null values. A null value represents either the absence of data or an unknown value. A null value is not the same as a zero or a blank.

Query 2-27 returns all rows that have a null paid_date, as Query Result 2-27 shows.

SELECT  order_num,  customer_num,  po_num,  ship_date

	FROM  orders

	WHERE  paid_date  IS  NULL

	ORDER  BY  customer_num


 






 Query Result 2-27 







 

To connect two or more comparison conditions, or Boolean expressions, use the logical operators AND, OR, and NOT. A Boolean expression evaluates as true or false or, if null values are involved, as unknown.

In Query 2-28, the operator AND combines two comparison expressions in the WHERE clause.

SELECT  order_num,  customer_num,  po_num,  ship_date

	FROM  orders

	WHERE  paid_date  IS  NULL

		AND  ship_date  IS  NOT  NULL

	ORDER  BY  customer_num

The query returns all rows that have null paid_date or a not null ship_date, as Query Result 2-28 shows.

Query Result 2-28

The following examples include a WHERE clause that searches for exact-text comparisons by using the keyword LIKE or MATCHES or the equal sign (=) relational operator. Unlike earlier examples, these examples illustrate how to query a table that is not in the current database. You can access a table that is not in the current database only if the database that contains the table has the same ANSI status as the current database. If the current database is an ANSI-compliant database, the table you want to access must also reside in an ANSI-compliant database. If the current database is not an ANSI-compliant database, the table you want to access must also reside in a database that is not an ANSI-compliant database.

Although the database used previously in this chapter is the demonstration database, the FROM clause in the following examples specifies the manatee table, created by the owner bubba, which resides in an ANSI-compliant database named syzygy. For more information on how to access tables that are not in the current database, see the Informix Guide to SQL: Syntax

Each statement in Query 2-29 retrieves all the rows that have the single word helmet in the description column, as Query Result 2-29 shows.

SELECT  *  FROM  syzygy:bubba.manatee

	WHERE  description  =  'helmet'

	ORDER  BY  mfg_code



SELECT  *  FROM  syzygy:bubba.manatee

	WHERE  description  LIKE  'helmet'

	ORDER  BY  mfg_code



SELECT  *  FROM  syzygy:bubba.manatee

	WHERE  description  MATCHES  'helmet'

	ORDER  BY  mfg_code 


 






 Query Result 2-29 

You can use the keywords LIKE and MATCHES for variable-text queries that are based on substring searches of fields. Include the keyword NOT to indicate the opposite condition. The keyword LIKE is the ANSI standard, whereas MATCHES is an Informix extension.

Variable-text search strings can include the wildcards listed with LIKE or MATCHES in the following table.

Important: You cannot test TEXT or BYTE columns with the LIKE or MATCHES keywords.

Also, when you use Dynamic Server, you cannot test BLOB or CLOB columns with the LIKE or MATCHES keywords.

The statements in Query 2-30 illustrate the use of a single-character wildcard in a WHERE clause. Further, they demonstrate a query on a table that is not in the current database. The stock table is in the database sloth. Besides being outside the current demonstration database, sloth is on a separate database server called meerkat.

For more information, see Selecting Tables from a Database Other Than the Current Database and the Informix Guide to SQL: Syntax.

SELECT  *  FROM  sloth@meerkat:stock

	WHERE  manu_code  LIKE  '_R_'

 		AND  unit_price  >=  100

	ORDER  BY  description,  unit_price



SELECT  *  FROM  sloth@meerkat:stock

	WHERE  manu_code  MATCHES  '?R?'

		AND  unit_price  >=  100

	ORDER  BY  description,  unit_price

Each statement in Query 2-30 retrieves only those rows for which the middle letter of the manu_code is R, as Query Result 2-30 shows.

Query Result 2-30

The comparison '_R_' (for LIKE) or '?R?' (for MATCHES) specifies, from left to right, the following items:

• Any single character
• The letter R
• Any single character

Query 2-31 selects only those rows where the manu_code begins with A through H and returns the rows that Query Result 2-31 shows. The test '[A-H]' specifies any single letter from A through H, inclusive. No equivalent wildcard symbol exists for the LIKE keyword.

SELECT  *  FROM  stock

	WHERE  manu_code  MATCHES  '[A-H]*'

	ORDER  BY  description,  manu_code,  unit_price 

 






 Query Result 2-31 

The statements in Query 2-32 use a wildcard at the end of a string to retrieve all the rows where the description begins with the characters bicycle.

SELECT  *  FROM  stock

	WHERE  description  LIKE  'bicycle%'

	ORDER  BY  description,  manu_code



SELECT  *  FROM  stock

	WHERE  description  MATCHES  'bicycle*'

	ORDER  BY  description,  manu_code 

Either statement returns the rows that Query Result 2-32 shows.

Query Result 2-32

The comparison 'bicycle%' or 'bicycle*' specifies the characters bicycle followed by any sequence of zero or more characters. It matches bicycle stem with stem matched by the wildcard. It matches to the characters bicycle alone, if a row exists with that description.

Query 2-33 narrows the search by adding another comparison condition that excludes a manu_code of PRC.

SELECT * FROM stock

	WHERE description LIKE 'bicycle%'

		AND manu_code NOT LIKE 'PRC'

	ORDER BY description, manu_code

The statement retrieves only the rows that Query Result 2-33 shows.

Query Result 2-33

When you select from a large table and use an initial wildcard in the comparison string (such as '%cycle'), the query often takes longer to execute. Because indexes cannot be used, every row is searched.

By default, Informix database servers use the U.S. English language environment, called a locale, for database data. This default locale uses the ISO 8859-1 code set. The U.S. English locale specifies that MATCHES will use code-set order.

If your database contains non-English data, the MATCHES clause uses the correct non-English code set for that language. Query 2-34 uses a SELECT statement with a MATCHES clause in the WHERE clause to search the table, abonnés, and to compare the selected information with the data in the nom column.

SELECT numéro,nom,prénom 

	FROM abonnés

	WHERE nom MATCHES '[E-P]*'

	ORDER BY nom

In Query Result 2-34, the rows for Étaix, Ötker, and Øverst are not selected and listed because, with ISO 8859-1 code-set order, the accented first letter of each name is not in the E through P MATCHES range for the nom column.

Query Result 2-34

Query 2-35 uses the keyword ESCAPE with LIKE or MATCHES so you can protect a special character from misinterpretation as a wildcard symbol.

SELECT  *  FROM  cust_calls

	WHERE  res_descr  LIKE  '%!%%'  ESCAPE  '!' 

The ESCAPE keyword designates an escape character (it is! in this example) that protects the next character so that it is interpreted as data and not as a wildcard. In the example, the escape character causes the middle percent sign (%) to be treated as data. By using the ESCAPE keyword, you can search for occurrences of a percent sign (%) in the res_descr column by using the LIKE wildcard percent sign (%). The query retrieves the row that Query Result 2-35 shows.

Query Result 2-35

You can use subscripting in the WHERE clause of a SELECT statement to specify a range of characters or numbers in a column, as Query 2-36 shows.

SELECT  catalog_num,  stock_num,  manu_code,  cat_advert,

         cat_descr

	FROM  catalog

	WHERE  cat_advert[1,4]  =  'High' 

The subscript [1,4] causes Query 2-36 to retrieve all rows in which the first four letters of the cat_advert column are High, as Query Result 2-36 shows.

Query Result 2-36

You can include a FIRST clause in a SELECT statement to specify that the query returns only a specified number of the first rows that match the conditions of the SELECT statement. You include a number immediately following the FIRST keyword to specify the maximum number of rows that the query can return. The rows that the database server returns when you execute a SELECT statement with a FIRST clause might differ, depending on whether the statement also includes an ORDER BY clause.

You cannot use a FIRST clause when the SELECT statement is a subquery or part of a view definition.

For information about restrictions on use of the FIRST clause, see the description of the SELECT statement in the Informix Guide to SQL: Syntax.

If you do not include an ORDER BY clause in a SELECT statement with a FIRST clause, any rows that match the conditions of the SELECT statement might be returned. In other words, the database server determines which of the qualifying rows to return, and the query result can vary depending on the query plan that the optimizer chooses.

Query 2-37 uses the FIRST clause to return the first five rows from the state table.

SELECT FIRST 5 *
	FROM  state 

 






 Query Result 2-37 

You can use a FIRST clause when you simply want to know the names of all the columns, and the type of data that a table contains, or to test a query that otherwise would return many rows. Query 2-38 shows how to use the FIRST clause to return column values for the first row of a table.

SELECT FIRST 1 *
	FROM  orders 

 






 Query Result 2-38 

You can include an ORDER BY clause in a SELECT statement with a FIRST clause to return rows that contain the highest or lowest values for a specified column. Query 2-38 shows a query that includes an ORDER BY clause to return (by alphabetical order) the first five states contained in the state table. Query 2-39, which is the same as Query 2-37 except for the ORDER BY clause, returns a different set of rows than Query 2-37.

SELECT FIRST 5 *
	FROM state ORDER BY sname 

 






 Query Result 2-39 

Query 2-40 shows how to use a FIRST clause in a query with an ORDER BY clause to find the 10 most expensive items listed in the stock table.

SELECT FIRST 10 description, unit_price
	FROM stock ORDER BY unit_price DESC 

 






 Query Result 2-40 

Enterprise Decision Server allows you to use the FIRST clause to select the first rows that result from a union query. Query 2-41 uses a FIRST clause to return the first five rows of a union between the stock and items tables.

SELECT FIRST 5 DISTINCT stock_num, manu_code
	FROM stock
	WHERE unit_price < 55.00


UNION 


SELECT stock_num, manu_code
	FROM items
	WHERE quantity > 3

 






 Query Result 2-41 

You are not limited to selecting columns by name. You can list an expression in the SELECT clause of a SELECT statement to perform computations on column data and to display information derived from the contents of one or more columns.

An expression consists of a column name, a constant, a quoted string, a keyword, or any combination of these items connected by operators. It can also include host variables (program data) when the SELECT statement is embedded in a program.

An arithmetic expression contains at least one of the arithmetic operators listed in the following table and produces a number.

Important: You cannot use TEXT or BYTE columns in arithmetic expressions.

With Dynamic Server, you cannot specify BLOB or CLOB in arithmetic expressions.

Arithmetic operations enable you to see the results of proposed computations without actually altering the data in the database. You can add an INTO TEMP clause to save the altered data in a temporary table for further reference, computations, or impromptu reports. Query 2-42 calculates a 7 percent sales tax on the unit_price column when the unit_price is $400 or more (but does not update it in the database).

SELECT  stock_num,  description,  unit,  unit_descr,

	    unit_price,  unit_price  *  1.07

	FROM  stock

	WHERE  unit_price  >=  400

If you are using DB-Access or the Relational Object Manager, the result appears in the expression column, as Query Result 2-42 shows.

Query Result 2-42

Query 2-43 calculates a surcharge of $6.50 on orders when the quantity ordered is less than 5.

SELECT  item_num,  order_num,  quantity,

             total_price,  total_price  +  6.50

	FROM  items

	WHERE  quantity  <  5 

If you are using DB-Access or the Relational Object Manager, the result appears in the expression column, as Query Result 2-43 shows.

Query Result 2-43

Query 2-44 calculates and displays in the expression column (if you are using DB-Access or the Relational Object Manager) the interval between when the customer call was received (call_dtime) and when the call was resolved (res_dtime), in days, hours, and minutes.

SELECT  customer_num,  user_id,  call_code,

             call_dtime,  res_dtime  -  call_dtime

	FROM  cust_calls

	ORDER  BY  user_id


 






 Query Result 2-44 

You can assign a display label to a computed or derived data column to replace the default column header expression. In Query 2-42, Query 2-43, and Query 2-44, the derived data appears in the expression column. Query 2-45 also presents derived values, but the column that displays the derived values has the descriptive header taxed.

SELECT  stock_num,  description,  unit,  unit_descr,

             unit_price,  unit_price  *  1.07  taxed

	FROM  stock

	WHERE  unit_price  >=  400

Query Result 2-45 shows that the label taxed is assigned to the expression in the select list that displays the results of the operation unit_price * 1.07.

Query Result 2-45

In Query 2-46, the label surcharge is defined for the column that displays the results of the operation total_price + 6.50.

SELECT  item_num,  order_num,  quantity,

             total_price,  total_price  +  6.50  surcharge

	FROM  items

	WHERE  quantity  <  5 

The surcharge column is labeled in the output, as Query Result 2-46 shows.

Query Result 2-46

Query 2-47 assigns the label span to the column that displays the results of subtracting the DATETIME column call_dtime from the DATETIME column res_dtime.

SELECT  customer_num,  user_id,  call_code,

             call_dtime,  res_dtime  -  call_dtime  span

	FROM  cust_calls

	ORDER  BY  user_id

The span column is labeled in the output, as Query Result 2-47

Query Result 2-47

A CASE expression is a conditional expression, which is similar to the concept of the CASE statement in programming languages. You can use a CASE expression when you want to change the way data is represented. The CASE expression allows a statement to return one of several possible results, depending on which of several condition tests evaluates to TRUE.

TEXT or BYTE values are not allowed in a CASE expression.

Consider a column that represents marital status numerically as 1, 2, 3, 4 with the corresponding values meaning single, married, divorced, widowed. In some cases, you might prefer to store the short values (1,2,3,4) for database efficiency, but employees in human resources might prefer the more descriptive values (single, married, divorced, widowed). The CASE expression makes such conversions between different sets of values easy.

The CASE expression also supports extended data types and cast expressions.

The following example shows a CASE expression with multiple WHEN clauses that returns more descriptive values for the manu_code column of the stock table. If none of the WHEN conditions is true, NULL is the default result. (You can omit the ELSE NULL clause.)

SELECT
	CASE
		WHEN manu_code = "HRO" THEN "Hero"
		WHEN manu_code = "SHM" THEN "Shimara"
		WHEN manu_code = "PRC" THEN "ProCycle"
		WHEN manu_code = "ANZ" THEN "Anza"
		ELSE NULL
	END
FROM stock 

You must include at least one WHEN clause within the CASE expression; subsequent WHEN clauses and the ELSE clause are optional. If no WHEN condition evaluates to true, the resulting value is null. You can use the IS NULL expression to handle null results. For information on handling null values, see the Informix Guide to SQL: Syntax.

The following example shows a simple CASE expression that returns a character string value to flag any orders from the orders table that have not been shipped to the customer.

SELECT order_num, order_date,
CASE 
	WHEN ship_date IS NULL
	THEN "order not shipped"
	END 
FROM orders


 






 Query Result 2-48 

For information about how to use the CASE expression to update a column, see Using a CASE Expression to Update a Column.

When you want to use ORDER BY as an expression, you can use either the display label assigned to the expression or an integer, as Query 2-49 and Query 2-50 show.

SELECT  customer_num,  user_id,  call_code,

             call_dtime,  res_dtime  -  call_dtime  span

	FROM  cust_calls

	ORDER  BY  span

Query 2-49 retrieves the same data from the cust_calls table as Query 2-47. In Query 2-49, the ORDER BY clause causes the data to be displayed in ascending order of the derived values in the span column, as Query Result 2-49 shows.

Query Result 2-49

Query 2-50 uses an integer to represent the result of the operation
res_dtime - call_dtime and retrieves the same rows that appear in Query Result 2-49.

SELECT  customer_num,  user_id,  call_code,

             call_dtime,  res_dtime  -  call_dtime  span

	FROM  cust_calls

	ORDER  BY  5

The database server assigns a unique rowid to rows in nonfragmented tables. However, rows in fragmented tables do not automatically contain the rowid column.

Informix recommends that you use primary keys as a method of access in your applications rather than rowids. Because primary keys are defined in the ANSI specification of SQL, using them to access data makes your applications more portable. In addition, the database server requires less time to access data in a fragmented table when it uses a primary key than it requires to access the same data when it uses rowid.

You can use a rowid to locate the internal record number that is associated with a row in a table. The rowid is, in effect, a hidden column in every table. The sequential values of rowid have no special significance and can vary depending on the location of the physical data in the chunk. Your rowid might vary from the following examples.

For more information about rowids, see the Informix Guide to Database Design and Implementation and your Administrator's Guide.

Query 2-51 uses the rowid and the wildcard asterisk symbol (*) in the SELECT clause to retrieve every row in the manufact table and their corresponding rowids.

SELECT  rowid,  *  FROM  manufact

 






 Query Result 2-51 







 

You can also use the rowid when you select a specific column, as Query 2-52 shows.

SELECT  rowid,  manu_code  FROM  manufact


 






 Query Result 2-52 







 

You can use the rowid in the WHERE clause to retrieve rows based on their internal record number. This method is handy when no other unique column exists in a table. Query 2-53 uses a rowid from Query 2-52.

SELECT  *  FROM  manufact WHERE  rowid  =  263

Query 2-53 returns the row that Query Result 2-53 shows.

Query Result 2-53

To obtain additional information about a table, you can combine the rowid with the USER function.

Query 2-54 assigns the label username to the USER expression column and returns this information about the cust_calls table.

SELECT  USER  username,  rowid  FROM  cust_calls


 






 Query Result 2-54 







 

You can also use the USER function in a WHERE clause when you select the rowid.

Query 2-55 returns the rowid for only those rows that are inserted or updated by the user who performs the query.

SELECT  rowid  FROM  cust_calls  WHERE  user_id  =  USER      

For example, if the user richc uses Query 2-55, the output is as Query Result 2-55 shows.

Query Result 2-55

You can add the DBSERVERNAME function (or its synonym, SITENAME) to a query to find out where the current database resides.

Query 2-56 finds the database server name and the user name as well as the rowid and the tabid, which is the serial-interval table identifier for system catalog tables.

SELECT  DBSERVERNAME  server,  tabid,  rowid,  USER  username  

	FROM  systables

	WHERE  tabid  >=  105 OR  rowid  <=  260

	ORDER  BY  rowid

Query 2-56 assigns display labels to the DBSERVERNAME and USER expressions and returns the 10 rows from the systables system catalog table, as Query Result 2-56 shows.

Query Result 2-56

Never store a rowid in a permanent table or attempt to use it as a foreign key because the rowid can change. For example, if a table is dropped and then reloaded from external data, all the rowids are different.