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:
begin-setup
declare-variable
date $temp1 $pay_begin_dt
end-declare
end-setup
…
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:
if &E.EARNS_END_DT < &A.PAY_BEGIN_DT
Column Variables
Code like this demonstrates all the options SQR gives us for variable usage. Rarely do we need all those options.
begin-select
J.EMPLID &J.EMPLID
move &J.EMPLID to $emplid
print $emplid (+1,1)
from PS_JOB J
end-select
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.
begin-select
J.EMPLID
print &J.EMPLID (+1,1)
from PS_JOB J
end-select
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:
begin-select
to_char(CHECK_DT, 'mm/dd/yyyy') &CHECK_MDY
from PS_PAY_CHECK
where EMPLID = $emplid
and PAY_END_DT = $pay_end_dt
end-select
The other case is when I want to clarify the meaning of the variable.
begin-select
D.DESCR &DEPTNAME
from PS_DEPT_TBL D
where D.SETID = $setid
and D.DEPTID = $deptid
and D.EFFDT = …
end-select
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.
begin-select
E.HOURLY_RT
E.ANNUAL_RT
let #annual_rt = cond(&J.ANNUAL_RT = 0, 2080 * &J.HOURLY_RT, &J.ANNUAL_RT)
from PS_EMPLOYEES E
end-select
Second, I might want to pass a value to a procedure from two calls, without making the procedure local.
begin-select
N.NAME
let $name = &N.NAME
do print_name
from PS_NAMES N
end-select
…
begin-select
DB.NAME
let $name = &DB.NAME
do print_name
from PS_DEP_BEN_NAME
end-select
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
or
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’
…
end-if
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’
…
end-if
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.
Recent Comments