Using 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 Creating and Using SPL Routines.

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

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

Figure 260 shows the output from this SPL routine.

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 Figure 261 contains a routine, conv_price, that converts the unit price of a stock item to a different currency and adds any import tariffs.

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