SPL routines in SELECT statements

Previous examples in this chapter show SELECT statement expressions that consist of column names, operators, and SQL functions. This section shows expressions that contain an SPL routine call.

SPL routines contain special Stored Procedure Language (SPL) statements as well as SQL statements. For more information on SPL routines, see Create and use SPL routines.

HCL Informix® allows you to write external routines in C and in Java™. For more information, see Informix® User-Defined Routines and Data Types Developer's Guide.

When you include an SPL routine expression in a projection list, the SPL routine must be one that returns a single value (one column of one row). For example, the following statement is valid only if test_func() returns a single value:

SELECT col_a, test_func(col_b) FROM tab1 
   WHERE col_c = "Davis";

SPL routines that return more than a single value are not supported in the Projection clause of SELECT statements. In the preceding example, if test_func() returns more than one value, the database server returns an error message.

SPL routines provide a way to extend the range of functions available by allowing you to 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. The following query 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. You could write a get_orders routine, which queries the orders table for each customer_num and returns the number of corresponding orders (labeled n_orders).

Figure 1. Query

SELECT customer_num, lname, get_orders(customer_num) n_orders
   FROM customer;

The result shows the output from this SPL routine.

Figure 2. Query result

customer_num    lname    n_orders

         101    Pauli           1
         102    Sadler          9
         103    Currie          9
         104    Higgins         4
        
         123    Hanlon          1
         124    Putnum          1
         125    Henry           0
         126    Neelie          1
         127    Satifer         1
         128    Lessor          0

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

Figure 3. Query

SELECT stock_num, manu_code, description FROM stock 
   WHERE conv_price(unit_price, ex_rate = 1.50, 
   tariff = 50.00) < 1000;