You are not limited to selecting columns by name. You can list an expression in the Projection 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.
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. Figure 86 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_price, unit_price * 1.07 FROM stock WHERE unit_price >= 400
The result appears in the expression column, as Figure 87 shows.
stock_num description unit_price (expression)
1 baseball gloves $800.00 $856.00
1 baseball gloves $450.00 $481.50
4 football $960.00 $1027.20
4 football $480.00 $513.60
7 basketball $600.00 $642.00
8 volleyball $840.00 $898.80
102 bicycle brakes $480.00 $513.60
111 10-spd, assmbld $499.99 $534.99
112 12-spd, assmbld $549.00 $587.43
113 18-spd, assmbld $685.90 $733.91
203 irons/wedge $670.00 $716.90Figure 88 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 The result appears in the expression column, as Figure 89 shows.
item_num order_num quantity total_price (expression)
1 1001 1 $250.00 $256.50
1 1002 1 $960.00 $966.50
2 1002 1 $240.00 $246.50
1 1003 1 $20.00 $26.50
2 1003 1 $840.00 $846.50
1 1004 1 $250.00 $256.50
2 1004 1 $126.00 $132.50
3 1004 1 $240.00 $246.50
4 1004 1 $800.00 $806.50
·
·
·
1 1023 2 $40.00 $46.50
2 1023 2 $116.00 $122.50
3 1023 1 $80.00 $86.50
4 1023 1 $228.00 $234.50
5 1023 1 $170.00 $176.50
6 1023 1 $190.00 $196.50Figure 90 calculates and displays in the expression column 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, call_code, call_dtime,
res_dtime - call_dtime
FROM cust_calls
ORDER BY customer_num
customer_num call_code call_dtime (expression)
106 D 1998-06-12 08:20 0 00:05
110 L 1998-07-07 10:24 0 00:06
116 I 1997-11-28 13:34 0 03:13
116 I 1997-12-21 11:24 5 20:55
119 B 1998-07-01 15:00 0 17:21
121 O 1998-07-10 14:05 0 00:01
127 I 1998-07-31 14:30You can assign a display label to a computed or derived data column to replace the default column header expression. In Figure 86, Figure 88, and Figure 92, the derived data appears in the expression column. Figure 92 also presents derived values, but the column that displays the derived values has the descriptive header taxed.
SELECT stock_num, description, unit_price,
unit_price * 1.07 taxed
FROM stock
WHERE unit_price >= 400Figure 93 shows that the label taxed is assigned to the expression in the projection list that displays the results of the operation unit_price * 1.07.
stock_num description unit_price taxed
1 baseball gloves $800.00 $856.00
1 baseball gloves $450.00 $481.50
4 football $960.00 $1027.20
4 football $480.00 $513.60
7 basketball $600.00 $642.00
8 volleyball $840.00 $898.80
102 bicycle brakes $480.00 $513.60
111 10-spd, assmbld $499.99 $534.99
112 12-spd, assmbld $549.00 $587.43
113 18-spd, assmbld $685.90 $733.91
203 irons/wedge $670.00 $716.90In Figure 94, 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 Figure 95 shows.
item_num order_num quantity total_price surcharge
1 1001 1 $250.00 $256.50
1 1002 1 $960.00 $966.50
2 1002 1 $240.00 $246.50
1 1003 1 $20.00 $26.50
2 1003 1 $840.00 $846.50
·
·
·
1 1023 2 $40.00 $46.50
2 1023 2 $116.00 $122.50
3 1023 1 $80.00 $86.50
4 1023 1 $228.00 $234.50
5 1023 1 $170.00 $176.50
6 1023 1 $190.00 $196.50Figure 96 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, call_code, call_dtime,
res_dtime - call_dtime span
FROM cust_calls
ORDER BY customer_numThe span column is labeled in the output, as Figure 97 shows.
customer_num call_code call_dtime span
106 D 1998-06-12 08:20 0 00:05
110 L 1998-07-07 10:24 0 00:06
116 I 1997-11-28 13:34 0 03:13
116 I 1997-12-21 11:24 5 20:55
119 B 1998-07-01 15:00 0 17:21
121 O 1998-07-10 14:05 0 00:01
127 I 1998-07-31 14:30A 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 IBM Informix: Guide to SQL Syntax.
Figure 98 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
order_num order_date (expression)
1001 05/20/1998
1002 05/21/1998
1003 05/22/1998
1004 05/22/1998
1005 05/24/1998
1006 05/30/1998 order not shipped
1007 05/31/1998
·
·
·
1019 07/11/1998
1020 07/11/1998
1021 07/23/1998
1022 07/24/1998
1023 07/24/1998For 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 on an expression, you can use either the display label assigned to the expression or an integer, as Figure 100 and Figure 102 show.
SELECT customer_num, call_code, call_dtime,
res_dtime - call_dtime span
FROM cust_calls
ORDER BY spanFigure 100 retrieves the same data from the cust_calls table as Figure 96. In Figure 100, the ORDER BY clause causes the data to be displayed in ascending order of the derived values in the span column, as Figure 101 shows.
customer_num call_code call_dtime span
127 I 1998-07-31 14:30
121 O 1998-07-10 14:05 0 00:01
106 D 1998-06-12 08:20 0 00:05
110 L 1998-07-07 10:24 0 00:06
116 I 1997-11-28 13:34 0 03:13
119 B 1998-07-01 15:00 0 17:21
116 I 1997-12-21 11:24 5 20:55Figure 102 uses an integer to represent the result of the operation res_dtime - call_dtime and retrieves the same rows that appear in Figure 101.
SELECT customer_num, call_code, call_dtime,
res_dtime - call_dtime span
FROM cust_calls
ORDER BY 4