SQR Evaluate Versus C/Java Switch, Part J*

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’:
    num_capitals++;
  case ‘a’:
    num_letter_a++;
  default:
    num_letter++;
};

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
end-evaluate

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
    stop
end-evaluate

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

end-evaluate

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
    break
! 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'
    stop
  when = $_prcs_run_cntl_id
    show 'RUN_CNTL_ID is null'
    stop
! 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-evaluate
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?

9 Comments

  1. Bob Josephson says:

    This should be Part K. The J did not exist in the Roman alphabet.

  2. Bob Josephson says:

    Holy tool misuse, Batman!

    First, the switch example with counting letters: yeah, it’ll work, but if I caught a C programmer writing that code, I’d have him shot. I realize, of course, that you’re not necessarily advocating people write code like that, but you’re using it as an example of what is possible.

    Next, the example of opening files, which seems less contrived than the letter counting: you’re right that a C switch statement can’t handle it, but is that such a bad thing? Your scenario has a logical flow of steps, so why not let that shine through in the code by using ‘if’ statements – even in SQR?

    And yet, I have to admit that’s a real nifty use of the evaluate. By giving ‘evaluate’ a constant of 0, and letting the ‘when’ statements do more work, it’s like turning a switch statement inside-out. Very cool (regardless of whether it’s good programming style or not).

    Your other examples are also intriguing uses of ‘evaluate’. I’m tempted to respond to them by saying “hey, that’s easy to do in C; you just wouldn’t use a switch statement to do it”. But of course, you’re not the one who originally likened ‘evaluate’ to ’switch’; the SQR manual did that.

    So your point that evaluate has very different abilities than switch is well taken and well supported. Nice article, Steve!

    - Bob

  3. Bob Josephson says:

    Oh, and one other thing: don’t forget Towers of Hanoi as a classic example of recursion. Surely, every HR department needs a good Towers of Hanoi implementation, eh?

    • administrator says:

      You forget, Peoplesoft also has financial, manufacturing, customer relations, and student administration software.

  4. Kevin says:

    I’ll try the brain teaser…

    The following can jump with return to either the same or next line:
    do
    print (and the implied print on a database column listed in a SELECT)
    print-image, print-chart
    position
    graphic box/horz-line/vert-line
    next-listing, next-column, new-page, new-report
    page-number, last-page, date-time
    begin-document
    connect
    execute
    end-program, end-report

    The following can jump with a return to some point nearby (but not the next line):
    begin-select (also listed below)
    begin-sql

    The following can jump without the possibility of return:
    goto
    when, when-other (evaluate)
    if, else
    while
    break
    exit-select
    end-procedure, end-heading, end-footing
    end-while
    begin-select
    end-select (or really the “from” line in the SQL)

    • administrator says:

      You’re welcome. And I hope you’ll say something more on your next comment; I’m trying to foster a discussion about SQR.

  5. kishore says:

    Hello….i do not know SQR…but i need to change SQR to SQL…please help me out…ty…

    IF PS_C_VCHR_CNTL_STG.ACCOUNTING_DT = $NULL_DT
    EVALUATE PS_ORIGIN_AP_OPT.ACCOUNTING_DT_IND
    WHEN = ‘D’
    EVALUATE PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT_IND

    WHEN = ‘S’
    MOVE PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT TO $ACCOUNTING_DT

    WHEN = ‘C’
    MOVE PS_C_CYCLE_TBL.C_CYCLE_DT TO $ACCOUNTING_DT
    END-EVALUATE

    WHEN = ‘S’
    MOVE PS_ORIGIN_AP_OPT.ACCOUNTING_DT TO $ACCOUNTING_DT
    WHEN = ‘C’
    MOVE PS_C_CYCLE_TBL.C_CYCLE_DT TO $ACCOUNTING_DT
    END-EVALUATE
    ELSE
    MOVE PS_C_VCHR_CNTL_STG.ACCOUNTING_DT TO $ACCOUNTING_DT
    IF PS_C_VCHR_HDR_STG.ACCOUNTING_DT > $ACCOUNTING_DT
    MOVE PS_C_VCHR_HDR_STG.ACCOUNTING_DT TO $ACCOUNTING_DT
    END-IF
    IF $ACCOUNTING_DT <= PS_C_CYCLE_TBL.C_CYCLE_DT

    SELECT B.OPEN_PERIOD_FROM
    FROM PS_C_MONTH_TBL A, PS_BU_LED_GRP_TBL B
    WHERE B.BUSINESS_UNIT = $BUSINESS_UNIT
    AND B.LEDGER_GROUP = ‘ACTUALS’
    AND B.OPEN_YEAR_FROM = A.YEAROFDATE
    AND B.OPEN_PERIOD_FROM <= A.MONTHOFDATE
    AND $ACCOUNTING_DT BETWEEN A.FROM_DATE AND A.THRU_DATE

    IF $PERIOD_OPEN_FOUND = ‘N’
    SELECT MON.FROM_DATE
    FROM PS_C_MONTH_TBL MON
    WHERE MON.YEAROFDATE = #YEAR of C_CYCLE_DT
    AND C_CYCLE_DT BETWEEN MON.FROM_DATE AND MON.THRU_DATE
    END-IF
    END-IF
    END-IF

    i need to change to sql….thanks in advance

    • administrator says:

      This is an interesting problem that would probably make use of the SQL case and decode functions. I’m busy working on assignments that my employer pays me to do, and on the next blog entry. Would anyone like to take this on?