Most programs are sprinkled with literal values; 11, ‘USA’, ’01-JUL-2007′, etc.  Sometimes we can guess their meanings from context.

while #sales_office <= 11
let $country = 'USA'
where effdt >= '01-JUL-2007'  ! The vision plan started on July 1, 2007

Sometimes we can’t.

let $end_date = dateadd($start_date, 'month', 11)
where erncd in ('USA','USB','USC')
where effdt >= '01-JUL-2007'  ! Select effdt after June 30, 2007

Why are we adding 11 months to $start_date?  What are the USA, USB, and USC earnings codes?  What happened on July 1, 2007?

The alternatives to literals are variables, dynamic variables (in SQL), and constants (substitution variables).  They have a value like literals and a name, which can give meaning to the value.


Variables can be scalars like &column, $date, #number, and $string, or structures like array.field(#index, #occurs) or %list(#index).  We discussed bad variable names in my March 22, 2009 entry, Bottom #Ten Worst Variable Names.  But good variable names can help document our programs;

let $end_date = dateadd($start_date, 'month', #standard_length_of_contract)
where erncd in ($domestic_airfare_reimbursement, $domestic_busfare_reimbursement, $domestic_carfare_reimbursement)
where effdt >= $first_day_of_policy17.8

But variables have the potential to vary; to take new values at any point in the program.  When we set the value of #standard_length_of_contract, there is no indication that it is a constant fact about the real world and not just a “work in process” whose value may change at any point.  If we set a variable in one part of the program and use it in another, a reader can’t know that we didn’t change the value somewhere in between without searching the entire program and any include files to be sure.

Another drawback is that we need some of these constant variables in commands that only take literals, like the size= parameter of create-array.


Constants are given a substitution value in #define commands and referenced by curly brackets.  The SQR compiler works like a text editor’s “replace” command.  It replaces one string of characters with another string of characters, even inside a quoted literal.

let $end_date = dateadd($start_date, 'month', {standard_length_of_contract))
where erncd in ({domestic_travel_erncds})
where effdt >= {first_day_of_policy17.8}

Note that {domestic_travel_erncds} replaces three variables.

Unlike variables, constants must be defined, and only once.  It’s easier to analyze a program with constants since we know their values don’t change.  They can be used anywhere a variable is used and also in places (begin-setup) and in commands (create-array, load-lookup, open) that don’t accept variables.

Unlike literals, constants have a name as well as a value, partially documenting our code.  That’s especially useful when the same value has different meanings in different contexts; there are 7 days in a week and perhaps 7 business units in the organization.  Constants should be named like variables, avoiding the bad practices listed in my earlier blog entry.

Sometimes, the literal value is not suitable for a constant itself.  Consider {standard_length_of_contract}, for which the compiler substitutes 11.  Our rule is that a contract beginning on January 1 will end on December 31.  January is month 1, December is month 12, so we add 11 months.

What we really mean is that the contract lasts one year (12 months), but the calculation of the end month requires adding 11 to the first month.  Perhaps it would be better to write

#define standard_length_contract 12
let $end_date = dateadd($start_date, 'month', {standard_length_of_contract) - 1)

There could be constants called {months_in_a_year} or {eggs_in_a_dozen} which would also equal 12.  “Eggs in a dozen” is obviously irrelevant and misleading, but we might think that these are yearly contracts, so the formula needs to know how many months are in a year.

There is a much greater chance of a business change that results in 6 month or 24 month contracts than a calendar change that results in a 13 month year.  If the contract length changed, despite the business analyst’s promise that it never would, we don’t want to change the value of a constant called {months_in_a_year}.  Also, names like {months_in_a_year} don’t explain why the number of months in a year is significant to this business process.

Enterprise Level Constants

We can create an include file with widely used constants.  This would give us consistencies across all programs and the ability to propagate policy changes.  Some constants would be definitional.

#define true 1
#define sunday 1
#define january 1
#define days_per_week 7

Some constants would establish facts about the organization.

#define pay_periods_per_year 24
#define company_name 'Acme Co.'
#define num_employees 10000
#define federal_ein '12-345678'
#define overtime_codes ‘OV1’,’OV2’

Some constants would support programming standardization.

#define phone_mask xxx/xxx-xxxx
#define output_file_dir /ftp/output/

I didn’t put quote marks around the values of these constants because they may be used without quotes, and it’s easier to add them to a constant than to remove them.  You might consider a standard rule that constants are never enclosed in quote marks.

print $phone_number (,50) edit {phone_mask}
let $formatted_phone = edit($phone_number, ‘{phone_mask}’)
let $fullname = ‘{output_file_dir}’ || $filename || $ext
let $fullname = ‘{output_file_dir}gen_ledger.dat’

Let’s Be A Community

This blog has recorded over 7,000 page reads since it began five months ago.  In the past week there were about 700 page reads from over 500 IP addresses.  I’m pleased and flattered by the attention, and I hope we can start something together.  There are only a few books about SQR, only a few aftermarket products, no magazines for us, no conferences.  We don’t know each other, we barely appear on the Internet.

Other languages have communities, and their programmers are better off for them.  I invite you all to rise above being an audience and speak out.  Who are you?  Where are you?  What do you do?  What do you want?