Guru: One-Shot Requests and Quoted Column Names

July 19, 2021 Ted Holt

If I had a dollar for every time someone has asked me to query data for them over the years, I could have retired already. (I did not say I would have, but that I could have.) It’s nice when the users can retrieve the information they need to do their jobs without help from IT, but when a request is too complex for them, I’m always glad to help.

I’ve used many tools over the years for one-shot requests for raw data. These days my tool of choice is the Run SQL Scripts tool, which is part of IBM Access Client Solutions (ACS). Run SQL Scripts allows me to save a result set in an Excel file, and the user can take it from there.

Unless they don’t understand the data in the columns, that is. This can happen when the first row of the saved spreadsheet contains the abbreviated field names that programmers are accustomed to working with. Think about it. Do you really want a user to call you asking what the column with RCMLL means after you’ve already turned your attention to another challenge?

There are a couple of ways to avoid this nuisance. First, you can define column headings for each field. In DDS, use the COLHDG keyword. In SQL, use LABEL ON COLUMN. Then, in Run SQL Scripts, start at the menu bar and select Edit > Preferences > Results. Set the Column headings drop-down box to LABEL. (See Figure 1.)

Figure 1: Displaying the column headings from the file object.

However, maybe nobody defined column headings for a database file or table that you’re querying. Maybe you’re using a software package and you aren’t permitted to add column headings. And even if you could add column headings, you don’t have time to do it at the drop of a hat. Or maybe you don’t like the column headings for one or more fields.

In such cases, you can use a column alias.

select i.ID       as "Item number",
       i.ItemName as "Item name",
       i.SRP      as "Catalog price"
  from items as i
 order by i.ID;   

Column aliases may be quoted or not. If I only use an alias within a query, I don’t quote the alias. But if the end user will see the alias, then I quote it. Quoting allows me to include blanks and other special characters, and that makes for a good description of the data in that column.

You do not have to use the word AS to separate the expression from the alias, but I always do. When I first learned SQL (1984 on an Oracle system), the AS keyword didn’t exist. I don’t know when it was added to the standard, but I immediately liked it the first time I saw it. Including the word AS makes queries easier for me to read.

When building a spreadsheet to give to a user, I set Run SQL Scripts to display the result set in a separate window. From the menu bar, select Edit > Preferences > Results > Open new results in a separate window. (See Figure 2.)

Figure 2: Displaying each result set in a separate window.

When I run the query, I can save the results from the new window. (If your version of ACS requires you to enable the option to save result sets, update to the latest version.) From the menu bar, select File > Save Results . . . Set the file type to Microsoft Excel (.xlsx) and enter a file name, browsing to the proper folder as appropriate. Check the Include column headings as the first line of output option.

When I save the file, the system responds that the file has been saved successfully and asks whether I would like to open it. At this point, I select Yes so that I can do one last good turn for the user. With the spreadsheet open, I select the first row by clicking on the row number on the left and clicking on the Wrap Text on the Home ribbon. I may adjust one or more column widths, but I don’t do much with the spreadsheet. The users are much more skillful with Excel than I am, and my only concern is to make the spreadsheet as understandable as possible to the user.

Here’s another scenario. Suppose one of the people we serve has told us to increase all prices by three percent. This person would like to review the before and after values before we update the database. Here’s a query that yields the correct information.

select i.ID          as "Item number",
       i.ItemName    as "Item name",
       i.SRP         as "Current price",
       i.SRP * 1.03  as "Price after 3% increase"
  from items as i
 order by i.ID;   

Notice that the current and new values are in adjacent columns. Figure 3 shows the result set in a spreadsheet with easily understood column headings in the first row.

Figure 3: Legible column headings

When the user tells me that the data looks good, I’m ready to update the database. With a bit of editing, I turn the query into an update. The important thing to me is that I don’t edit the column expressions.

update items set
select i.ID          as "Item number",
       i.ItemName    as "Item name",
       i.SRP =      as "Current price",
       i.SRP * 1.03  as "Price after 3% increase"
 from items as i
 order by i.ID;

One last example. Suppose the new price is not calculated, but taken from a database table, most likely uploaded from a spreadsheet. Here’s the query that shows the user the current and updated prices with descriptive column headings.

select i.ID          as "Item number",
       i.ItemName    as "Item name",
       i.SRP         as "Current price",
       u.NewPrice    as "New price"
  from items as i
  join PriceUpd as u
    on i.ID = u.ID
 order by i.ID;

In this case, a bit of editing allows me to create the SET clause of the MERGE statement without rekeying the expressions.

merge into items as i
 using (select * from PriceUpd) as u
    on i.ID = u.ID
  when matched then
     update set i.SRP = u.NewPrice;

Does it really matter how descriptive the column names are? I believe it does. My years in the workplace have convinced me that poor communication is a far greater cause of failure than lack of technical skills. Anything that I can do to enhance communication with the people I serve is valuable. Remember this query?

select i.ID          as "Item number",
       i.ItemName    as "Item name",
       i.SRP         as "Current price",
       i.SRP * 1.03  as "Price after 3% increase"
  from items as i
 order by i.ID;   

Figure 4 shows what the spreadsheet would look like without and with the aliases. Which is easier to understand?

Figure 4: A spreadsheet without (left) and with (right) column aliases.

Other than building descriptive column headings, I’ve never found a reason to use quoted aliases. But that reason is sufficient. If you know another reason, please share it with the rest of us.