Last week I paid tribute to Elements of Style. The lesson from that book that impressed me most was to simplify my prose; minimize adjectives and adverbs; replace clichéd phrases with single words; avoid passive voice and inverted sentences; be terse and direct. I recommend the same for programming.

Don’t Follow Just Any Example

People often learn SQR by example. The Peoplesoft course on SQR programming recommended that when we have to write a program, we start with an already written program and modify it. Our Peoplesoft products came with hundreds of SQR programs that were written and debugged a long time ago. Their age means that they may not use the “newer” features, like date variables introduced in November 1996.

Excessive Setup

Many SQR programs begin with four subroutine calls, to Init-DateTime, Get-Current-DateTime, Init-Number, and StdAPI-Init. I skip the first three.

Init-DateTime sets a collection of variables that can be used by the other procedures in datetime.sqc that format dates or do date arithmetic. I prefer to use the built-in date functions because they can be embedded in formulae, unlike SQR procedures, and they are documented in manuals, unlike SQR procedures.

Get-Current-DateTime queries the database to get the current time. It also initializes a slew of string variables ($AsOfToday, $AsOfNow, $CurrentCentury, $ReportDate, etc.). I didn’t know that, did you? I use the SQR built-in variable $current-date or the built-in function datenow().

Init-Number sets a few variables that can be used by the other procedures in number.sqc that format monetary amounts. I prefer to use the masks that are built-in to the move, print, and show commands, and are also available with the edit() function.

Old Dates

My SQR Dates article celebrated the date variables and functions that have graced SQR for almost 13 years. We still have Peoplesoft delivered programs that struggle with strings that must be interpreted as dates. Here is a snippet of code:

do Format-DateTime(&E.EARNS_END_DT, $temp1, {DEFCMP}, '', '')
do Format-DateTime(&A.PAY_BEGIN_DT, $Pay_Begin_Dt, {DEFCMP}, '', '')
if $temp1 < $Pay_Begin_Dt

The purpose of this code is clear from context, but I could never remember the order and meaning of the Format-DateTime parameters while I was writing new code. Imagine my surprise in 1999 to learn that I had just been taught techniques that were three years out of date. Modern code could be like this:

    date $temp1 $pay_begin_dt

let $temp1 = strtodate(&E.EARNS_END_DT, 'dd-mon-yyyy')
let $Pay_End_Dt = strtodate(&A.PAY_BEGIN_DT, 'dd-mon-yyyy')
if $temp1 < $Pay_Begin_Dt

But SQR will treat a date column as a date variable as well as a string variable, depending on context. (How many other compilers read our minds like that?) We can skip the setup block, the string to date conversion, and the other variables, and write:


Column Variables

Code like this demonstrates all the options SQR gives us for variable usage. Rarely do we need all those options.

  move &J.EMPLID to $emplid
  print $emplid (+1,1)
 from PS_JOB J

SQR allows us to select a column from a database table, then transfer the value (once) to another column variable, then copy the value to an ordinary date, number, or string variable. Each of those operations can be useful, but in this case they were redundant. We could have written this.

  print &J.EMPLID (+1,1)
 from PS_JOB J

SQR will create a column variable with the alias and name of the database table column by default. If we are satisfied with that variable name, we shouldn’t create it explicitly. When we create it explicitly, we risk a misspelling and we force our reader to take a second look to see the variable name.

There are two cases in which I rename a column variable. SQR requires it if we select a formula rather than a column:

to_char(CHECK_DT, 'mm/dd/yyyy')    &CHECK_MDY
where EMPLID = $emplid
  and PAY_END_DT = $pay_end_dt

The other case is when I want to clarify the meaning of the variable.

where D.SETID = $setid
  and D.DEPTID = $deptid
  and D.EFFDT = …

We can use column variables anywhere we use other variables. There are two cases for which I copy a column variable to another variable. First, if I might want to change the value.

  let #annual_rt = cond(&J.ANNUAL_RT = 0, 2080 * &J.HOURLY_RT, &J.ANNUAL_RT)

Second, I might want to pass a value to a procedure from two calls, without making the procedure local.

  let $name = &N.NAME
  do print_name
 from PS_NAMES N

  let $name = &DB.NAME
  do print_name

Excessive Variable Use

let $drive = 'c:'
let $path = '\output\benefits\'
let $filename = 'medical'
let $ext = '.dat'
let $today = datenow()
let $month = edit($today, 'mm')
let $year = edit($today, 'yyyy')
let $fullfilename = $drive || $path || $filename || $year || $month || $ext


let $fullfilename = 'c:\output\benefits\medical\ ' || edit(datenow(), 'yyyymm') || '.dat'

I’m assuming that $drive, $path, $filename, $ext, $today, $month, $year are not used anywhere else in the program. Well, maybe $today is. Why don’t you review the two thousand line program plus the three include files to see whether the other six variables may be excluded. I’ll wait. Are you back now? Didn’t find anything, right? Isn’t it better not to worry future readers about variables that don’t need to exist?

Insufficient Variable Use

Sometimes it’s good to use a variable even when it isn’t necessary. Consider this code, where date variable $today contains October 4, 2009.

if edit($today, ‘MON’) = ‘JAN’ or edit($today, ‘MON’) = ‘FEB’ or edit($today, ‘MON’) = ‘MAR’

There are many ways we could improve this code, but the point I’m making here is that we could write:

let $month = edit($today, ‘MON’)
if $month = ‘JAN’ or $month = ‘FEB’ or $month = ‘MAR’

Using a variable simplies the compound test, and it allows us to use an evaluate command or to pass the value to a procedure.

A Contest

As I said, there are many ways to improve my example above. I invite two or more people to see how many improved versions they can find. I’m not looking for the best possible code, just any code that is better. Each person should leave a comment with one improved variation. Let’s see who can keep going the longest.