Common Table Expressions Ease System Conversion
September 24, 2008 Dear Occupant
In the factory where I work, we are replacing one engineering module of the large ERP package we run with a more modern, more robust engineering module. What was one physical file is now two or three physical files. Modifying programs and queries to use the new files is just about more fun than I ever envisioned when I was learning to write RPG at the vo-tech many years ago. Fortunately, I have some great tools to help with the conversion. One of them is the common table expression. I’ll show you what I mean.
Assume a customer master file, CUSTOMERS, is being replaced with two files–TCUST (the customer master) and TCUSTSHIP (customer ship-to master). Assume also the following SQL command in use somewhere in the system (e.g., embedded in an RPG program):
select cus.cusno, cus.cusnam, cus.billcity, cus.billstate, cus.shipcity, cus.shipstate, cmt.cmttext from customers as cus left join comments as cmt on cus.cusno = cmt.cusno
Notice that this query uses two files–the customer master file and a comments file.
What must I do to the query in order to replace CUSTOMERS with TCUST and TCUSTSHIP? I could rewrite the query to use the new files. However, I found out quickly that approach can get complicated, especially in my project, where one file may be replaced by three or four files.
I like to make things as easy as possible, and I was able to easily change SQL queries by developing common table expressions that create result sets that look like the old files. Here’s the example from above, with a common table expression.
with customers as (select c.custacct as cusno, c.custname as cusnam, c.btcity as billcity, c.btstate as billstate, s.shpcity as shipcity, s.shpstate as shipstate from tcust as c join tcustship as s on s.custcomp = 1 and c.custcomp = s.custcomp and c.custacct = s.custacct and s.primary = 1) select cus.cusno, cus.cusnam, cus.billcity, cus.billstate, cus.shipcity, cus.shipstate, cmt.cmttext from customers as cus left join comments as cmt on cus.cusno = cmt.cusno
SQL no longer retrieves database file CUSTOMERS, but uses the common table expression called CUSTOMERS in its place. The common table expression selects the data from TCUST and TCUSTSHIP and renames the selected columns to the names they have in the old CUSTOMERS file. Now that the common table expression has been developed, we can copy it to other SQL queries as needed, although we may have to change the list of columns in the SELECT clause.
Thanks to common table expressions, I have every reason to believe that my project will be completed successfully and on time.