• 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

Using the Asterisk Symbol (*)

Query 2-5a

SELECT  manu_code,  manu_name,  lead_time

	FROM  manufact

Query 2-5b

SELECT  *  FROM  manufact

Reordering the Columns

Query 2-6

SELECT  manu_name,  manu_code,  lead_time

	FROM  manufact 

Sorting the Rows

An explicit select list, shown in Query 2-7a, includes all the column names that you want to retrieve.

Query 2-7a

SELECT  manu_code,  manu_name,  lead_time

	FROM  manufact

	ORDER  BY  lead_time  

Query 2-7b

SELECT  *  FROM  manufact

	ORDER  BY  lead_time

Ascending Order

Descending Order

Query 2-8

SELECT  *  FROM  manufact

	ORDER  BY  lead_time  DESC 

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

Sorting on Multiple Columns

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.

Query 2-9

SELECT  *  FROM  stock

	ORDER  BY  manu_code,  unit_price

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

Query 2-10

SELECT  *  FROM  stock

	ORDER  BY  unit_price,  manu_code

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

Query 2-11

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  

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

Query 2-12

SELECT  customer_num  FROM  orders 

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, include the keyword DISTINCT or its synonym UNIQUE at the start of the select list, as Query 2-13 shows.

Query 2-13

SELECT  DISTINCT  customer_num  FROM  orders



SELECT  UNIQUE  customer_num  FROM  orders

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.

Query 2-14

SELECT  po_num  FROM  orders

However, the list is not in a very 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.

Query 2-15

SELECT  po_num  FROM  orders

	ORDER  BY  po_num

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.

Query 2-16

SELECT  paid_date,  ship_date,  order_date,  

	   customer_num,  order_num,  po_num

	FROM  orders

	ORDER  BY  paid_date,  order_date,  customer_num

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.

Query 2-17

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

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

Query 2-18

SELECT  customer_num,  order_num,  po_num,  order_date

	FROM  orders

	ORDER  BY  4  DESC,  1

GLS

ORDER BY and Non-English Data

If your database contains non-English data, the ORDER BY clause should return data in the order appropriate to that language. Query 2-19 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.

Query 2-19

SELECT numéro,nom,prénom 

	FROM abonnés

	ORDER BY nom;

• 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. Store non-English data in NCHAR (or NVARCHAR) columns to obtain results sorted by the language.

• Whether the database server is using the correct non-English locale when accessing 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-19 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. Refer to the Guide to GLS Functionality for more information on non-English data and locales.

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

Query Result 2-19a

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

Query Result 2-19b

Query Result 2-19b shows that when the appropriate locale file is referenced by the data server, names starting with 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.

Selecting Substrings

Query 2-20

SELECT  zipcode[1,3],  customer_num  

	FROM  customer

	ORDER  BY  zipcode

Using the WHERE Clause

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 set of rows returned by a SELECT statement is its active set. A singleton SELECT statement returns a single row. Use a cursor to retrieve multiple rows in an SQL API. See Chapter 5, "Programming with SQL," and Chapter 6, "Modifying Data Through SQL Programs."

Creating a Comparison Condition

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.

Operator	Operation
=	equals
!= or <>	does not equal
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to

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 Chapter 1 of 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. You cannot use CLOB, BLOB, TEXT, or BYTE columns in string expressions, except when you 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

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

Including Rows

Query 2-21

SELECT  customer_num,  call_code,  call_dtime,  res_dtime

	FROM  cust_calls

	WHERE  user_id  =  'maryj'  

Excluding Rows

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 complete discussion of owner naming, see Chapter 1 in the Informix Guide to SQL: Syntax.

Query 2-22

SELECT  customer_num,  company,  city,  state

	FROM  odin.customer

	WHERE  state  !=  'CA'



SELECT  customer_num,  company,  city,  state

	FROM  odin.customer

	WHERE  state  <>  'CA'

Specifying Rows

Query 2-23

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

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 display TEXT and BYTE values when you write an SQL API application to do so.

Excluding a Range of Rows

Query 2-24

SELECT  fname,  lname,  company,  city,  state

	FROM  customer

	WHERE  zipcode  NOT  BETWEEN  '94000'  AND  '94999'

	ORDER  BY  state 





Query Result 2-24 






 

Query 2-25

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

You cannot test a CLOB, BLOB, TEXT or BYTE column 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.

Query 2-26

SELECT  lname,  city,  state,  phone

	FROM  Aleta.customer

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

	ORDER  BY  state 

Identifying Null Values

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

Query 2-27

SELECT  order_num,  customer_num,  po_num,  ship_date

	FROM  orders

	WHERE  paid_date  IS  NULL

	ORDER  BY  customer_num

Forming Compound Conditions

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

Query 2-28

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

Using Variable-Text Searches

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

Symbol	Meaning
LIKE
%	Evaluates to zero or more characters
_	Evaluates to a single character
\	Escapes special significance of next character
MATCHES
*	Evaluates to zero or more characters
?	Evaluates to a single character (except null)
[ ]	Evaluates to a single character or range of values
\	Escapes special significance of next character

You cannot test a CLOB, BLOB, TEXT, or BYTE column with LIKE or MATCHES.

Using Exact Text Comparisons

An external table is a table that is not in the current database. You can access only external tables that are part of an ANSI-compliant database.

Whereas the database used previously in this chapter was the demonstration database called stores7, 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 defining external tables, see Chapter 1 in 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.

Query 2-29

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 

Using a Single-Character Wildcard

For details on external tables and external databases, see Chapter 1 in the Informix Guide to SQL: Syntax.

Query 2-30

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

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

SELECT  *  FROM  stock

	WHERE  manu_code  MATCHES  '[A-H]*'

	ORDER  BY  description,  manu_code,  unit_price

WHERE Clause with Variable-Length Wildcard

Query 2-32

SELECT  *  FROM  stock

	WHERE  description  LIKE  'bicycle%'

	ORDER  BY  description,  manu_code



SELECT  *  FROM  stock

	WHERE  description  MATCHES  'bicycle*'

	ORDER  BY  description,  manu_code 

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.

Query 2-33

SELECT * FROM stock

	WHERE description LIKE 'bicycle%'

		AND manu_code NOT LIKE 'PRC'

	ORDER BY description, manu_code

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.

GLS

MATCHES and Non-English Data

If your database contains non-English data, the MATCHES clause should use 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.

Query 2-34

SELECT numéro,nom,prénom 

	FROM abonnés

	WHERE nom MATCHES '[E-P]*'

	ORDER BY nom;

In Query Result 2-34b, 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. The database server uses code-set order when the nom column is CHAR data type. It also uses localized ordering when the column is NCHAR data type, and you specify a nondefault locale.

Query Result 2-34a

In Query Result 2-34b, the rows for Étaix, Ötker, and Øverst are included in the list because the database server uses a locale-specific comparison.

Query Result 2-34b

For more information on non-English data and locales, refer to the Guide to GLS Functionality.

Comparing for Special Characters

Query 2-35

SELECT  *  FROM  cust_calls

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

Using Subscripting in a WHERE Clause

Query 2-36

SELECT  catalog_num,  stock_num,  manu_code,  cat_advert,

         cat_descr

	FROM  catalog

	WHERE  cat_advert[1,4]  =  'High' 

Expressions and Derived Values

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.

Arithmetic Expressions

Operator	Operation
+	addition
-	subtraction
*	multiplication
/	division

Operations of this nature 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-37 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).

Query 2-37

SELECT  stock_num,  description,  unit,  unit_descr,

	    unit_price,  unit_price  *  1.07

	FROM  stock

	WHERE  unit_price  >=  400

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

Query 2-38

SELECT  item_num,  order_num,  quantity,

             total_price,  total_price  +  6.50

	FROM  items

	WHERE  quantity  <  5 

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

Query 2-39

SELECT  customer_num,  user_id,  call_code,

             call_dtime,  res_dtime  -  call_dtime

	FROM  cust_calls

	ORDER  BY  user_id

Using Display Labels

Query 2-40

SELECT  stock_num,  description,  unit,  unit_descr,

             unit_price,  unit_price  *  1.07  taxed

	FROM  stock

	WHERE  unit_price  >=  400

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

Query 2-41

SELECT  item_num,  order_num,  quantity,

             total_price,  total_price  +  6.50  surcharge

	FROM  items

	WHERE  quantity  <  5 

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

Query 2-42

SELECT  customer_num,  user_id,  call_code,

             call_dtime,  res_dtime  -  call_dtime  span

	FROM  cust_calls

	ORDER  BY  user_id

Sorting on Derived Columns

Query 2-43

SELECT  customer_num,  user_id,  call_code,

             call_dtime,  res_dtime  -  call_dtime  span

	FROM  cust_calls

	ORDER  BY  span

Query 2-44 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-43.

Query 2-44

SELECT  customer_num,  user_id,  call_code,

             call_dtime,  res_dtime  -  call_dtime  span

	FROM  cust_calls

	ORDER  BY  5

Expressions supported include aggregate, function (which include arithmetic functions), constant, and column expressions. These expressions are described in Chapter 1 of the Informix Guide to SQL: Syntax.

Aggregate Functions

Aggregates are often used to summarize information about groups of rows in a table. This use is discussed in Chapter 3, "Composing Advanced SELECT Statements." When you apply an aggregate function to an entire table, the result contains a single row that summarizes all of the selected rows.

Query 2-45 counts and displays the total number of rows in the stock table.

Query 2-45

SELECT  COUNT(*)

	FROM  stock

Query 2-46 includes a WHERE clause to count specific rows in the stock table; in this case, only those rows that have a manu_code of SHM.

Query 2-46

SELECT  COUNT (*)

	FROM  stock

	WHERE  manu_code  =  'SHM'

Query 2-47

SELECT  COUNT (DISTINCT  manu_code)

	FROM  stock

Query 2-48 computes the average unit_price of all rows in the stock table.

Query 2-48

SELECT  AVG (unit_price)

	FROM  stock

Query 2-49 computes the average unit_price of just those rows in the stock table that have a manu_code of SHM.

Query 2-49

SELECT  AVG (unit_price)

	FROM  stock

	WHERE  manu_code  =  'SHM'

You can combine aggregate functions as Query 2-50 shows.

Query 2-50

SELECT  MAX (ship_charge),  MIN (ship_charge)

	FROM  orders

You can apply functions to expressions, and you can supply display labels for their results, as Query 2-51 shows.

Query 2-51

SELECT MAX (res_dtime  -  call_dtime)  maximum,

	MIN (res_dtime  -  call_dtime)  minimum,

	AVG (res_dtime  -  call_dtime)  average

	FROM  cust_calls

Query 2-52 calculates the total ship_weight of orders that were shipped on July 13, 1994.

Query 2-52

SELECT  SUM (ship_weight)

	FROM  orders

	WHERE  ship_date  =  '07/13/1994' 

Time Functions

Using DAY and CURRENT

Query 2-53

SELECT  customer_num,  DAY (call_dtime),  DAY (res_dtime)

	FROM  cust_calls 

Query 2-54 uses the DAY and CURRENT functions to compare column values to the current day of the month. It selects only those rows where the value is earlier than the current day.

Query 2-54

SELECT  customer_num,  DAY (call_dtime),  DAY (res_dtime)

	FROM  cust_calls

	WHERE  DAY (call_dtime)  <  DAY (CURRENT)

Query 2-55 shows another use of the CURRENT function, selecting rows where the day is earlier than the current one.

Query 2-55

SELECT  customer_num,  call_code,  call_descr

	FROM  cust_calls

	WHERE  call_dtime  <  CURRENT  YEAR  TO  DAY 

Using MONTH

Query 2-56

SELECT customer_num,

	MONTH (call_dtime)  call_month,  

	MONTH (res_dtime)  res_month

	FROM  cust_calls

Query 2-57 uses the MONTH function plus DAY and CURRENT to show what month the customer call was received and resolved if DAY is earlier than the current day.

Query 2-57

SELECT customer_num,

	MONTH (call_dtime)  called,  

	MONTH (res_dtime)  resolved

	FROM  cust_calls

	WHERE  DAY (res_dtime)  <  DAY (CURRENT) 

Using WEEKDAY

Query 2-58

SELECT customer_num,  

	WEEKDAY (call_dtime)  called,  

	WEEKDAY (res_dtime)  resolved  

	FROM  cust_calls

	ORDER  BY  resolved 

Query 2-59 uses the COUNT and WEEKDAY functions to count how many calls were received on a weekend. This kind of statement can give you an idea of customer-call patterns or indicate whether overtime pay might be required.

Query 2-59

SELECT  COUNT(*)  

	FROM  cust_calls

	WHERE  WEEKDAY (call_dtime)  IN  (0,6) 

Query 2-60 retrieves rows where the call_dtime is earlier than the beginning of the current year.

Query 2-60

SELECT customer_num,  call_code,  

	YEAR (call_dtime)  call_year,

	YEAR (res_dtime)  res_year

	FROM  cust_calls

	WHERE  YEAR (call_dtime)  <  YEAR (TODAY)  





Query Result 2-60 

Query 2-61

SELECT customer_num,  

	EXTEND (call_dtime,  month  to  minute)  call_time,

	EXTEND (res_dtime,  month  to  minute)  res_time

	FROM  cust_calls

	ORDER  BY  res_time

Using the DATE Function

Query 2-62

SELECT  customer_num,  call_dtime,  res_dtime

	FROM  cust_calls

	WHERE  call_dtime  >  DATE ('12/31/93')

Query 2-63 converts DATETIME values to DATE format and displays the values, with labels, only when call_dtime is greater than or equal to the specified date.

Query 2-63

SELECT  customer_num,

	DATE (call_dtime)  called,  

	DATE (res_dtime)  resolved

	FROM  cust_calls

	WHERE  call_dtime  >=  DATE ('1/1/94') 





Query Result 2-63 

You can use these functions and keywords to select an expression that consists entirely of constant values or an expression that includes column data. In the first instance, the result is the same for all rows of output.

In addition, you can use the HEX function to return the hexadecimal encoding of an expression, the ROUND function to return the rounded value of an expression, and the TRUNC function to return the truncated value of an expression.

In Query 2-64, the LENGTH function calculates the number of bytes in the combined fname and lname columns for each row where the length of company is greater than 15.

Query 2-64

SELECT  customer_num,

	LENGTH (fname) + LENGTH (lname)  namelength

	FROM  customer

	WHERE  LENGTH (company)  >  15  





Query Result 2-64 






 

The USER function can be handy when you want to define a restricted view of a table that contains only your rows. For information on creating views, see Chapter 11, "Granting and Limiting Access to Your Database," in this manual and the GRANT and CREATE VIEW statements in Chapter 1 of the Informix Guide to SQL: Syntax.

Query 2-65a specifies the USER function and the cust_calls table.

Query 2-65a

SELECT  USER  from  cust_calls 

Query 2-65b

SELECT  *  FROM  cust_calls

	WHERE  user_id  =  USER 

If Query 2-66 is issued when the current system date is July 10, 1994, it returns this one row.

Query 2-66

SELECT  *  FROM  orders

	WHERE  order_date  =  TODAY 

You can include the keyword DBSERVERNAME (or its synonym, SITENAME) in a SELECT statement in Universal Server to find the name of the database server. You can query on the DBSERVERNAME for any table that has rows, including system catalog tables.

In Query 2-67, you assign the label server to the DBSERVERNAME expression and also select the tabid column from the systables system catalog table. This table describes database tables, and tabid is the serial-interval table identifier.

Query 2-67

SELECT  DBSERVERNAME  server,  tabid  FROM  systables

	WHERE  tabid  <=  4  





Query Result 2-67 

In Query 2-68, the HEX function returns the hexadecimal format of three specified columns in the customer table.

Query 2-68

SELECT  HEX (customer_num)  hexnum,  HEX  (zipcode)  hexzip,  

	HEX  (rowid)  hexrow  

	FROM  customer 





Query Result 2-68 

Using SPL Routines in SELECT Statements

SPL routines contain special Stored Procedure Language (SPL) statements as well as SQL statements. For more information on SPL routines, refer to Chapter 14, "Creating and Using SPL Routines."

SPL routines provide a way to extend the range of functions available; you can perform a subquery on each row you select.

For example, suppose you want a listing of the customer number, the customer's last name, and the number of orders the customer has made. Query 2-69 shows one way to retrieve this information. The customer table has customer_num and lname columns but no record of the number of orders each customer has made. The following query assumes you have written a get_orders routine, which queries the orders table for each customer_num and returns the number of corresponding orders that is labeled n_orders.

Query 2-69

SELECT customer_num, lname, get_orders(customer_num) n_orders

		FROM customer

Use SPL routines to encapsulate operations that you frequently perform in your queries. For example, the condition in Query 2-70 contains a routine, conv_price, that converts the unit price of a stock item to a different currency and adds any import tariffs.

Query 2-70

SELECT stock_num, manu_code, description FROM stock 

	WHERE conv_price(unit_price, ex_rate = 1.50, tariff = 50.00) < 1000