The SQR Language Reference, which I keep under my pillow, states “[t]his command is equivalent to case/switch in C or Java.”  Wrong!  The SQR evaluate command is much better than the switch command in C and Java.  Don’t be so modest, SQR.

*I think we should always use Roman numerals when discussing information technology in the XXI century.  Please pass the abacus, I need to compile my program.

The Syntax of Switch and Evaluate

The C and Java switch command starts with the word switch, followed by a test value in parentheses, and a curly open-bracket.  (Or should I say a open curly-bracket?)  The SQR evaluate command starts with the word evaluate, followed by a test value.

switch (value) {
versus
evaluate #value

C and Java allow the test value to be a literal, a variable, an array element, the object of a pointer, the return value of a function, or an expression that combines any of these objects.  SQR allows the test value to be a literal, a variable, or a database column – not as flexible.  Score: C/Java 1, SQR 0.

Next, C and Java have zero or more branches.  Each branch starts with the word case, followed by a space, followed by a value, followed by a colon.  SQR has zero or more branches too.  Each branch starts with the word when, followed by a comparison operator, followed by a value, followed by a carriage-return/line-feed (Windows) or just a line-feed (Unix).

case 1:
versus
when = 1

It looks similar, but SQR goes far beyond this.  C and Java are limited to an implied equals sign.  SQR allows equals (=), not-equals (<> or !=), greater than (>), greater than or equals (>=), less than (<), or less than or equals (<=).  Score: C/Java 1, SQR 1.

Also, C and Java branch values are limited to simple literals.  I don’t have a C or Java compiler handy, but every example I could find was integers or single characters.  (Strings are not scalar data types in C and Java; they are arrays of characters.)  An SQR test value or branch value can be a number, string, or date and it can be a literal, variable, or column.  Score: C/Java 1, SQR 2.

One reason that C and Java require simple literals for the branch values is that the compiler needs to enforce uniqueness – only one branch for each value.  That will make sense in the next section, Behavioral Differences, but SQR allows the same value for multiple branches.  That enables some interesting algorithms, which we’ll sample next week.

A block of code can follow the case 1: or when = 1.  If there are multiple case branches or when branches before a block of code, any of those conditions will select that code.

If the block of code contains a break command (in C, Java, or SQR), the program will terminate the switch or evaluate command and proceed to the code that follows.

Next, C and Java can end with a branch that executes if none of the preceding branches do.  It starts with the word default, followed by a colon.  SQR can end with a similar branch.  It starts with the words when-other, followed by a carriage return/line feed or just a line feed.

default:
versus
when-other

Finally, the C and Java switch command ends with a curly close-bracket.  The SQR evaluate command ends with the words end-evaluate on a separate line.

};
versus
end-evaluate

Behavioral Differences

In C or Java, the switch command works like this:

  1. Evaluate the switch value.
  2. Go to the first case.
  3. If this is the default, execute the commands and exit the switch command.
  4. Compare the switch value to the case value.
  5. If they don’t match, go to the next case and go to step 3.
  6. Otherwise, execute all following commands until a break command or the end of the switch command.  Disregard any case or default commands.  (Default can get executed in two contradictory ways.  First, if there are no matches.  Second, if there is a match … and there are no break statements after the match.  Is this good or bad?  Yes.)

There’s no point for C or Java to allow more than one of the same case values.  Program flow branches to the first matching value and potentially executes all the code that follows.

In SQR, the evaluate command works like this:

  1. Evaluate the evaluate value.
  2. Go to the first when.
  3. If this is the when-other branch and no other when has been true, execute the commands and exit the evaluate command.  If this is the when-other branch and any other when has been true, exit the evaluate command without executing the commands.
  4. Compare the evaluate value to the when value.
  5. If the comparison is true, execute the commands for that when branch.
  6. Go to the next when and step 3.

The same comparison can appear in more than one SQR branch.  With inequality operators, different comparisons can be true.  For example 5 is less than 10, and less than 15, and greater than 0.

Although there are some clever things we can do with the C/Java behavior, the SQR behavior is much more flexible and powerful.  Score: C/Java 1, SQR 3.  I don’t like the C/Java default behavior, but that can be cured by a break command right before it – so no penalty.

Looking Ahead

Next week, we’ll continue the competition with the algorithms that switch and evaluate enable.  Hint: SQR is going to run up the score.

Brain Teaser

Meanwhile, here is a brainteaser.  Please post solutions as comments.

Background: The Peoplesoft Enterprise Payroll module has a table called PS_EARNINGS_TBL.  That table contains an effective dated list of earnings codes.  The key fields are ERNCD and EFFDT.  ERNCD is a three character field with values like “REG” for regular pay, “OVT” for overtime, and “VAC” for vacation.  EFFDT is the effective date of the information on its row.  It allows us to keep the history of the earnings codes and make statements about changes; i.e. “on January 1, 2009 we added VOT to give employees paid time off to vote.”

Challenge: Write a “Begin-Select” block that will select one row for each ERNCD.  The row should be:

  1. The latest row (according to EFFDT) before the $As_Of_Date if the ERNCD was in use before the $As_Of_Date.
  2. The earliest row (according to EFFDT) after the $As_Of_Date if the ERNCD did not start to be used until after the $As_Of_Date.