Last week, in “SQR Evaluate Versus C/Java Switch, Part I,” we considered the overly modest claim that evaluate was equivalent to switch. Comparing the syntax of the two commands favored evaluate with a score of C/Java 1, SQR 3. Now the competition moves to the “usefulness” test.

*Last week I implied that the previous post, “Part I,” was using Roman numerals. Actually, I was using the Roman alphabet (ancient Latin). The letter after “I” is “J.” True or false?

Counting Letters

We have an array of characters; a string. Every character in the string is a letter of the alphabet, either upper case or lower case. We want to count the number of letters; the length of the string. We also want to count the number of letters that are either upper or lower case “A.” We also want to count the number of letters that are upper case “A.”

In C or Java, we’ll create a loop, pick each element of the array, and move it to a character variable called “letter.” Then we’ll execute this switch command. (When a variable is followed by two plus signs, it means “add 1 to that variable.”)

switch (letter) {
  case ‘A’:
  case ‘a’:

This is pretty clever, concise, and powerful. Thanks to the “fall through” feature, if the variable “letter” is an upper case “A”, it will increment the counter for capital letters, the counter for letters equal to “a”, and the counter for any letter. If the variable is a lower case “a”, it will increment the counter for letters equal to “a” and the counter for any letter. If the variable is any other letter, it will only increment the counter for letters.

But it won’t look so pretty if we try to extend it to the rest of the alphabet, or if we try to support digits or other characters. At that point it becomes clear that a switch statement wasn’t the right technology for this problem.

For the sake of the competition, here is the SQR evaluate version.

evaluate $letter
  when = ‘A’
    add 1 to #num_capitals
  when = ‘A’
  when = ‘a’
    add 1 to #num_letter_a
  when = ‘a’
  when <> ‘a’
    add 1 to #num_letter

Whoa! When equal to ‘a’ or not equal to ‘a’? Is that the best we can do to execute a branch in every case? Point to C/Java. Score: C/Java 2, SQR 3.

Opening Files

We need to open three files. If we fail to open any one of the files, we shouldn’t bother opening the subsequent ones, and we should indicate which one failed. However, we should try the root directory and the temp directory for the “input.txt” file.

evaluate 0
  when = 0
    move ‘c:\input.txt’ to $filename
    open $filename as 1 for-reading record=100:vary status=#status
  when <> #status
    move ‘c:\temp\input.txt’ to $filename
    open $filename as 1 for-reading record=100:vary status=#status
  when = #status
    move ‘c:\temp\output.txt’ to $filename
    open $filename as 2 for-reading record=150:fixed status=#status
  when = #status
    move ‘c:\temp\error.txt’ to $filename
    open $filename as 3 for-reading record=50:vary status=#status
  when <> #status
    show ‘cannot open ‘ $filename

Switch cannot compare test values to variables (ding!) and switch cannot change the action at a later branch based on what happened in a previous branch (ding!). Score: C/Java 2, SQR 5.

The evaluate command simplifies the program structure. We only need one copy of the error handling code. We don’t have to nest a series of “if #status = 0” commands. When this approach is feasible, it allows us to weave through multiple blocks of code with a minimum of control structure overhead.

Finding The Earliest Date

In Peoplesoft HR, the PS_JOB table keeps track of an employee’s current DEPTID, POSITION_NBR, JOBCODE, GRADE, and STEP. It also has the dates on which the current values of those fields were first effective; DEPT_ENTRY_DT, POSITION_ENTRY_DT, JOB_ENTRY_DT, GRADE_ENTRY_DT, and STEP_ENTRY_DT. What was the most recent change?

declare-variable date $latest_dt

let $latest_dt = strtodate(’01-jan-1900’, ‘dd-mon-yyyy’)

evaluate $latest_dt
  when < &dept_entry_dt
    move &dept_entry_dt to $latest_dt
  when < &job_entry_dt
    move &job_entry_dt to $latest_dt


Switch cannot find minimums or maximums because (1) the case tests cannot be inequalities, (2) the case values cannot be variables, and (3) after the first matching branch, the following case values are ignored and all the code in the following branches is executed until the break command. Score: C/Java 2, SQR 6.

Sure, I wish SQR had max() and min() functions that could take any number of number, string, or date arguments. But at least we have evaluate. Note that the inequality operators only work for dates when we use date variables or formats that put the four digit year first, then the two digit month (January is “01”), then the two digit day (the first is “01”).

Validating Inputs

In a future post, I will share my technique for encapsulating the process of receiving run control parameters. Here is a preview that highlights the evaluate command.

This procedure, get_parameter, takes two inputs. First, the name of a column in a run control table, to be used if the SQR program is running from the Peoplesoft Process Scheduler. Second, a string to display to the user for requesting input from the keyboard, to be used if the SQR program is running from the client PC. The procedure returns the value from the run control row or the keyboard.

Remember, procedures with arguments (or parameters) automatically default to local variables. If we want to use a global variable (i.e. $prcs_oprid), we must put an underscore after the first character ($, #, or &).

begin-procedure get_parameter($column_name, $input_prompt, :$value)
move '' into $value
evaluate ''
! If global variable $prcs_process_instance is null, then we are
! running from the client PC and should use the input command.
  when = $_prcs_process_instance
    show $input_prompt noline
    input $value noprompt
! Otherwise, we are running from the Peoplesoft Process Scheduler
! and we need the global variables $prcs_oprid and $prcs_run_cntl_id
  when = $_prcs_oprid
    show 'OPRID is null'
  when = $_prcs_run_cntl_id
    show 'RUN_CNTL_ID is null'
! There is a way for an SQR to figure out its run control table.
! Watch for a future post. We’re going to get the table name and
! keep it in global variable $run_control_table. But we only need
! to do it once, for the first run control parameter we want.
  when = $_run_control_table
    do get_run_control_tablename
! Now we’ll get the value, but only if there is a column name. We
! don’t want an SQL error.
  when <> $column_name
    do get_column_value($column_name, $value)
end-procedure get_parameter

This example is conceptually similar to the “opening files” or the “find the earliest date” examples. We have several blocks of code, each one dependent on the blocks before it and the comparison controlling it. But we’ve gone beyond the parallel construction of the previous examples. The first block performs data entry. The next two blocks validate data. The fourth block performs a SQL select, but only once. The fifth block uses data validated or obtained by its predecessors to get the run control value.

We could have done this without the evaluate command. We could have done this in C or Java, but definitely not with the switch command. Final score: C/Java 2, SQR 7. Despite the SQR Language Reference, the evaluate command is NOT equivalent to the C/Java switch command. It is better. QED.

Request for Information

Have you done something unusual with the evaluate command? Please share it with us.

Looking Ahead

There is an area where C and Java beat SQR; support for recursion. The two most famous recursive algorithms are factorials and quicksort. We don’t need factorials very often in the enterprise software world, but it would be nice to sort an array. Next week, I will present an SQR version of quicksort.

Brain Teaser

Meanwhile, here is a brain teaser. Please post solutions as comments.

Normally, SQR programs flow from the first line to the second line, to the third line, and so on. Some commands, like evaluate, change the flow, allowing the program to skip lines. Other commands, like do, change the flow, jumping to another part of the program and allowing the program to return to the line right after the do.

Who can list the most SQR commands that jump to another part of the program with the possibility of return? Who can list the most SQR commands that jump to another part of the program without the possibility of return?