Conditional SQL I/O, Take 2
March 20, 2017 Ted Holt
Bob writes, “Hey, Ted! I hope you can teach an old dog a new trick. I am trying to replace the CHAIN operation with SQL. I chain once to a file to read a certain record. If that record is not found, I chain again to retrieve a default record. How can I make SQL do a second read to the same file?”
This is not a hard thing to do, as SQL has no problem joining more than once to the same table. I’ll show you two methods to retrieve your data. The first method is the easier one, but you can only use it if the table cannot store null values in the columns (fields) that you’re retrieving. (Such is the case in many IBM i shops.) The second method works whether or not the columns can contain null values.
Let’s set up the situation by defining two physical files – a customer master file (CUSTMAST) and a customer addresses file (CUSTADDR). The former has one record per customer.
A UNIQUE A R MASTREC A ID 3 0 A NAME 24 A K ID
The customer address file may have one or two rows per customer, each distinguished by the address type code.
A UNIQUE A R ADDRREC A ID 3 0 A TYPE 1 A ADDRESS1 16 A ADDRESS2 16 A CITY 16 A STATE 2 A ZIPCODE 10 A K ID A K TYPE
The address type has the value 1 for a billing address and 2 for a shipping address. To keep this example as simple as possible, assume that all customers have a billing address, but only some customers have a shipping address. If a customer has no shipping address, we ship to the billing address.
Let’s read the customer master and list the shipping addresses. To simplify the example even more, I omit the city, state, and ZIP (postal) code. (They would be handled in the same way the two address line columns are handled.)
Here’s the customer address data:
Here’s some RPG code that is typical of many shops:
FCustMast if e k disk FCustAddr if e k disk Fqsysprt o f 132 printer C AddrKey klist C kfld ID C kfld AddrType 1 C C read MastRec 90 C dow *in90 = *off C eval AddrType = '2' C AddrKey chain AddrRec 91 C if *in91 = *on C eval AddrType = '1' C AddrKey chain AddrRec 91 C endif C if *in91 = *on C clear Address1 C clear Address2 C endif C except pline C read MastRec 90 C enddo C C eval *inlr = *on Oqsysprt e pline O ID O Name + 1 O Address1 + 1 O Address2 + 1
Notice the two CHAIN operations to CUSTADDR. If the first CHAIN does not find the shipping address, the program chains again to retrieve the billing address. Now, how do we do the same sort of thing using SQL?
Method 1
The first method only works if the columns cannot contain null values. Here’s the query:
select m.ID, m.Name,
coalesce (s.Address1, b.Address1) as Address1,
coalesce (s.Address2, b.Address2) as Address2
from CustMast as m
left join CustAddr as s
on (m.ID = s.ID and s.type = '2')
left join CustAddr as b
on (m.ID = b.ID and b.type = '1')
Notice that the customer address table is joined twice. The first join retrieves the shipping address (type 2). The second join retrieves the billing address (type 1). If the shipping address does not exist, the columns from the type 2 row will be null.
Notice also the COALESCE functions. COALESCE selects the first non-null value from a list. Since the Address1 and Address2 columns cannot store null values, the query selects the billing address if the shipping address is not in the table.
As I said, this query will not work if null values are permitted in the address fields. Here’s what the query would return if null were allowed and used in address line columns:
Notice Dostoyevsky. His first address line comes from the shipping record, but the second address line comes from the billing record. We need a way to determine whether the shipping address exists. That turns out to be very easy to do.
Method 2
Here are the same customer master and customer address tables defined the modern way.
create table CustMast
( ID dec(3) not null,
Name varchar(24),
primary key (ID))
create table CustAddr
( ID dec (3) not null,
Type char(1) not null,
Address1 varchar(16),
Address2 varchar(16),
City varchar(16),
State char(2),
ZipCode char(10),
primary key (ID, Type))
The key columns cannot have a null value, but the other columns may. Here’s the same customer address data, but this time with nulls:
Here’s the query:
select m.ID, m.Name,
case when s.ID is not null
then s.Address1
else b.Address1 end as Address1,
case when s.ID is not null
then s.Address2
else b.Address2 end as Address2
from CustMast as m
left join CustAddr as s
on (m.ID = s.ID and s.type = '2')
left join CustAddr as b
on (m.ID = b.ID and b.type = '1')
Instead of COALESCE functions, I use CASE expressions, but notice what the CASE expressions test. To make this work, you must test one of the columns of the primary key from the row that may or may not exist. I chose to test the ID column from the shipping address (s.ID). This test tells me whether the shipping address exists because the only way a column of a primary key can be null is when the join does not find a match.
Here are the results.
Notice that Dostoyevsky’s address came out right this time.
This scenario is similar to a one I wrote about a few years ago, but in that tip, the second join was to a different file and I didn’t consider null values.
Ted, doing it this way you don’t have to be concerned about nulls.
select m.ID, m.Name, a.address1, a.address2
from CustMast as m
left join table( select address1, address2
from CustAddr a
where a.ID = m.ID
and a.type in (‘1’, ‘2’)
order by a.ID, a.type desc
fetch first 1 rows only
) as a on 1=1
where m.ID = someID
That’s a good idea too, Rusty, although things like “on 1=1” always look strange to me.