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?
This should be Part K. The J did not exist in the Roman alphabet.
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
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?
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)
You forget, Peoplesoft also has financial, manufacturing, customer relations, and student administration software.
Thank you!
You’re welcome. And I hope you’ll say something more on your next comment; I’m trying to foster a discussion about SQR.
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
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?