Arithmetic expressions

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

Operator: Operation
+: addition
-: subtraction
*: multiplication
/: division

You cannot use TEXT or BYTE columns in arithmetic expressions.

With HCL Informix®, 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. The following query 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).

Figure 1. Query

SELECT stock_num, description, unit_price, unit_price * 1.07
   FROM stock
   WHERE unit_price >= 400;

The result appears in the expression column.

Figure 2. Query result

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

The following query calculates a surcharge of $6.50 on orders when the quantity ordered is less than 5.

Figure 3. Query

SELECT item_num, order_num, quantity,
       total_price, total_price + 6.50
   FROM items
   WHERE quantity < 5;

The result appears in the expression column.

Figure 4. Query result

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

The following query 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.

Figure 5. Query

SELECT customer_num, call_code, call_dtime, 
       res_dtime - call_dtime
   FROM cust_calls
   ORDER BY customer_num;

Figure 6. Query result

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:30