Conditional SQL I/O
June 5, 2013 Ted Holt
If an RPG programmer has a best friend, it may very well be the CHAIN opcode, which performs a random read on a keyed database file. It’s common programming practice in information-processing shops to read one file sequentially and to chain to read a host of others randomly. SQL handles this sort of thing differently. With SQL, joining is the proper way to retrieve data from two or more tables and/or views. The difference is even more pronounced when some of the random processing may or may not take place.
Suppose you’re responsible for a database that contains customer orders. Each order is for one item, and the price of an item comes from one of two tables: a standard pricing table (the catalog prices), and a customer-specific pricing table. The usual way to retrieve the price is to look for (randomly read) a customer-specific price first, and if none is found, then and only then look for (randomly read) the standard price. In RPG terms, it’s like this:
dow '1'; read order; if %eof(); leave; endif; // look for a price for this customer & item chain (custid: itemid) cusprice; if %found(); SellingPrice = Price; else; // there was no price for this customer // get the catalog price chain itemid PriceRec; if %found(); SellingPrice = Price; else; // there is no price for item // perform error routine endif; endif; enddo;
But SQL contains no such conditional logic to control I/O. In an SQL world, the way to handle such a situation is to read everything, whether you need it or not. This is counterintuitive to an old RPG or COBOL programmer, but it works, and it works just fine.
Take a look at this SQL command:
select ord.ordernbr, ord.custid, ord.itemid, cp.price, prc.price, coalesce(cp.price, prc.price) as SellingPrice from orders as ord left join prices as prc on ord.itemid = prc.itemid left join cusprices as cp on ord.custid = cp.custid and ord.itemid = cp.itemid
The primary table, ORDERS, is the one that would typically be read sequentially in an RPG or COBOL program. PRICES (the standard price table) and CUSPRICES (the customer price table) are both included as secondary tables. The conditional logic–giving preference to the customer price over the standard price–is carried out with the COALESCE function.
COALESCE takes two or more arguments and returns the first one that is not null. In this case, if the customer price (cp.price) is not null, the customer price is the selling price. Otherwise, the standard price (prc.price) is the selling price. The result set illustrates this behavior.
Notice that customer price overrides the standard price for orders 102 and 105.
Let’s complicate the example a bit.
Suppose that a customer can belong to another customer (sometimes called a parent or a group). If a customer has negotiated a specific price for an item, that price is to be used in invoicing. If there is no customer-specific price, then the parent’s (group) price is to be used, if such exists. Otherwise, we’re back to the standard price.
The concept is the same. All necessary tables are joined to form the result set, and COALESCE picks the first non-null of the three values.
select ord.ordernbr, ord.custid, cus.custgrp, ord.itemid, cp.price, gp.price, prc.price, coalesce(cp.price, gp.price, prc.price) as UnitPrice from orders as ord left join prices as prc on ord.itemid = prc.itemid left join cusprices as cp on ord.custid = cp.custid and ord.itemid = cp.itemid left join customers as cus on ord.custid = cus.custid left join cusprices as gp on cus.cusgrp = gp.custid
Table CUSPRICES is used twice in the query; once for a customer (CP) and once for the customer’s group (GP). Here’s the result set:
Customer price comes from the CUSPRICES row (record) for the customer. Group price comes from the CUSPRICES row for the parent. COALESCE picks the first non-null of the three values, forcing customer price to override the parent price and parent price to override the standard price.
I’ll mention one more thing, and then I’m done. If this query were from a real production system, I would not include the customer price, group price, and standard price in the result set unless there was a need to do so. The only price I would typically include would be the selling price. I would probably not include the customer group either.
The fact that I use a column (field) in a query doesn’t mean that I have to put it in the result set.