Common Table Expressions Ease System Conversion

Published: 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.

--Ted

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot