• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, Access Client Solutions, ACS, FHG, Four Hundred Guru, IBM i, Run SQL Scripts, SQL

    Sponsored by
    Maxava

    Migrating to a new IBM Power System?

    Whether it be Power8, Power9 or Power10 – Maxava has you covered

    Our migration service moves data from the old to the new server without disruption while the business continues to operate without impacting performance. Our service avoids long periods of downtime and means businesses can reduce the risk of moving to new hardware.

    To learn more about Maxava’s migration service, call us on 888 400 1541 or VISIT maxava.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: Delusionware How IBM i Fits Into a Zero-Trust Security Framework

    One thought on “Guru: One-Shot Requests and Quoted Column Names”

    • Bill Pahl says:
      July 19, 2021 at 1:55 pm

      I’ve always had an issue with the column headings in Excel, even though I have them set. This article made me curious. Like you have shown above, I prefer Name and Label output so I don’t want to keep changing my preferences for users. What I found by a little experimentation is that I can leave my preferences alone and and the View>Column Headings on the output window only. When set to Label, or Label and Name, the columns heading have the text only. However when Name and Label is used, only the field names appear. Thanks for making me try it out!

      Reply

    Leave a Reply Cancel reply

TFH Volume: 31 Issue: 47

This Issue Sponsored By

  • ProData
  • Profound Logic
  • Comarch
  • Raz-Lee Security
  • WorksRight Software

Table of Contents

  • Time To Design – And Deliver – The Application System/360
  • How IBM i Fits Into a Zero-Trust Security Framework
  • Guru: One-Shot Requests and Quoted Column Names
  • As I See It: Delusionware
  • IBM i PTF Guide, Volume 23, Number 29

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Power10 Entry Machines: The Power S1024 And Power L1024
  • Thoroughly Modern: Latest IT Trends – Bring Security, Speed, And Consistency To IT With Automation
  • Big Blue Unveils New Scalable VTL For IBM i
  • As I See It: Thank God It’s Thursday
  • IBM i PTF Guide, Volume 24, Number 32
  • JD Edwards Customers Face Support Decisions
  • Security, Automation, and Cloud Top Midrange IT Priorities, Study Says
  • Cleo and SrinSoft in Integration-Modernization Link Up
  • Four Hundred Monitor, August 3
  • IBM i PTF Guide, Volume 24, Number 31

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.