SQR Without Literals

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?


  1. Bob Josephson says:

    Constants are great, but when I look at the code, it would be nice to know instantly whether an element is a variable or a constant. In C, the convention (not enforced by the compiler) is to put constants in all caps. Would that be of value in SQR?

    As for who I am… I’m Bob Josephson. Aren’t I?

    As for what I want… as Kosh would say, “never ask that question.”

    • administrator says:

      It’s a good idea to give visual cues, but the variable vs. constant issue is already clear in SQR. Variables must begin with #, $, %, or &, and they are the only objects that may begin with those characters. Constants must be enclosed with curly brackets {}, and they are the only objects that may be enclosed with those characters.

      Thank you for giving us your name, Bob. But as we saw on Babylon 5, that doesn’t answer the question “Who are you?”

  2. Kevin says:

    A minor quibble, but constants can be defined multiple times and their values can change, although fortunately this is rare. What seems to confuse people at first is that constants are resolved at “compile” time and so putting a #DEFINE under an IF, for example, is sort of nonsense. I’ve seen code like this:

    #define a_value_my_proc_will_use 1
    do My-Proc
    #define a_value_my_proc_will_use 2
    do My-Proc

    As to the other item–hi from:
    Kevin Reschenberg
    Independent consultant in PeopleSoft technical work (and a former PeopleSoft employee)
    Orange County, California
    sparkpath.com and sqr-info.com

    Thanks for your very interesting blog. I really appreciate the level of detail and well-thought-out opinions.

    • administrator says:

      Kevin, it’s great to hear from you again, and thanks for the encouragement. I’m really surprised to see your code sample. I have a memory from long ago of the compiler complaining about defining something twice. Was that just for constants defined to exist like below, or am I thinking of another language entirely?

      #define UNIX

      • Kevin says:

        Yes, either type of #DEFINE can be done twice. (What might not be clear to people reading my code sample is that it’s LEGAL but BAD. The constant value will be 2 in both calls to My-Proc assuming that My-Proc is defined later in the file.) I suppose that it doesn’t matter to the compiler if there are multiple definitions of a constant, unlike for example multiple definitions of a procedure.

        • Bob Josephson says:

          I don’t know if anyone who actually works on the SQR compiler is reading this, but perhaps the ability to redefine constants should be deprecated (meaning that in the next version of the compiler, it generates warnings and, in future versions it generates errors).

          • administrator says:

            There are a variety of old commands that the SQR Language Reference lists as “not recommended.” They have been deprecated for at least ten years but the old syntax is still supported.