Guru: Odds and Ends

August 16, 2021 Ted Holt

I really appreciate the comments that readers add to the end of articles or send to me in email. The more you share, the more all of us are better off. I know that you don’t have time to go back through articles we have published to see if anyone has commented, so this week I do that for you. Please keep the feedback coming!

Several people posted responses to Paul Tuohy’s article Getting Meaningful Audit Information from a Journal. You can read them for yourself, but I wanted to thank Emanuele, who mentioned a tool that is similar in function to the stored procedure that Paul shared with us. The tool is called EXPJRNE, and it is available from tools400.de. Many times have I been glad that I had more than one tool that did the same sort of thing, as I sometimes one more suitable in some situations and another tool preferable in other situations.

I encourage you to spend some time studying Paul’s SQL PL code. He uses some interesting techniques that are worth mastering.

Concerning The Uncertainty of Redundant Row Selection, Anoop pointed out that a system-created index may explain why a redundant row-selection test might have improved the performance of an SQL query. This explanation makes a lot of sense and I am grateful to Anoop for bringing it to my attention. My mind remains unsettled on the matter of redundant row selection.

In response to Guru: Elapsed Time For Human Beings, Jose Walker was kind enough to point out that SQL has scalar functions that can extract the pieces of a duration. After I ran his code — it worked, of course — I retrofitted his example into one of mine.

With JobOn as
   (select a.Order, a.Operation, a.Stamp as JobOnTime
      from session.Opers as a
     where a.Transaction = 'JOBON' ),
JobOff as
   (select b.Order, b.Operation, b.Stamp as JobOffTime
      from session.Opers as b
     where b.Transaction = 'JOBOFF'),
Combined as     
   (select on.Order, on.Operation, on.JobOnTime, off.JobOffTime,
           cast (off.JobOffTime - on.JobOnTime as decimal (20, 6))
              as Elapsed     
      from       JobOn  as on
      inner join JobOff as off
         on (on.Order, on.Operation) = (off.Order, off.Operation))
select c.Order, c.Operation, c.JobOnTime,
       case when year(c.Elapsed)   = 0 then ''
            else trim(char(year(c.Elapsed)))   concat ' years '
        end concat
       case when month(c.Elapsed)  = 0 then ''
            else trim(char(month(c.Elapsed)))  concat ' months '
        end concat
       case when day(c.Elapsed)    = 0 then '' 
            else trim(char(day(c.Elapsed)))    concat ' days '
        end concat
       case when hour(c.Elapsed)   = 0 then ''
            else trim(char(hour(c.Elapsed)))   concat ' hours '
        end concat
       case when minute(c.Elapsed) = 0 then ''
            else trim(char(minute(c.Elapsed))) concat ' minutes '
        end concat
       case when second(c.Elapsed) = 0 then ''
            else trim(char(second(c.Elapsed))) concat ' seconds '
        end as Elapsed
  from Combined as c 
      order by c.Order, c.JobOnTime

I won’t say that I find it pretty, but it certainly works. If I decide to use these functions, I’ll probably include them in a function of my own.

These functions are a bit picky. To make them work with durations, I had to cast the duration to a packed decimal value. Zoned decimal won’t work.

By the way, getting Jose’s code to run wasn’t easy. WordPress did weird things with the hyphens and apostrophes, and it took me a while to find the invalid characters. I ended up pasting his SQL statement into good ol’ green-screen STRSQL and looking for question marks. I have to admit that I did not find STRSQL obsolete that day.

Jay was kind enough to offer an improvement, or at least an alternative, to the technique I used in Guru: Stub Testing And SQL. He pointed out that adding a common table expression to the beginning of the query would leave the existing query intact.

with states (Abbreviation, Name) as 
   (values ('TX', 'Texas'))
select c.CusNum, c.LstNam,                    
       c.City, coalesce(s.Name,c.State) as State,
       c.ZIPCod               
  from qcustcdt as c                          
  left join states as s
    on c.State = s.Abbreviation
 order by c.CusNum;

It had never occurred to me to put my stub table into a common table expression, but I must say I like that idea a lot. It makes the query use row value expressions rather than the table or view with less modification to the query itself.

I appreciated hearing from faithful reader Mike, who emailed me in response to my article about qualified files. He wanted to know how this technique compare to the use of the PREFIX keyword in F specs.

I used the PREFIX keyword heavily before IBM introduced qualified files. In fact, qualified file support had been out a quite a while before I finally adopted it. I suppose it took me a while to get around to learning how to use them. Am I the only one who has trouble keeping up with the new features?

Both qualified files and the PREFIX keyword method allow us to give unique names to the fields. This is wonderful because the compiler allocates different areas of memory to two fields of the same name that reside in different files. The advantage of qualified files is that we can make all the fields local to subprocedures, rather than global in scope. This is of tremendous value to me, as I am on what is probably a quixotic quest to put global variables on the endangered species list.

As we would all benefit from more varied content in this august publication, I am looking for new authors. If you would like to contribute an article or merely have an idea for an article, please get in touch with me through the IT Jungle contact page.

RELATED STORIES

Guru: Elapsed Time For Human Beings

Guru: Stub Testing And SQL

Guru: The Uncertainty of Redundant Row Selection

Guru: Getting Meaningful Audit Information from a Journal

Guru: Qualified Files – Underused and Unappreciated