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:
- Evaluate the switch value.
- Go to the first case.
- If this is the default, execute the commands and exit the switch command.
- Compare the switch value to the case value.
- If they don’t match, go to the next case and go to step 3.
- 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:
- Evaluate the evaluate value.
- Go to the first when.
- 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.
- Compare the evaluate value to the when value.
- If the comparison is true, execute the commands for that when branch.
- 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:
- The latest row (according to EFFDT) before the $As_Of_Date if the ERNCD was in use before the $As_Of_Date.
- 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.
The C switch and the SQR Evaluate are, perhaps, intended to serve different purposes. The C switch can frequently be compiled to a single instruction: take the test value, use it as an offset into a table of addresses, and jump indirect. (That can be a single instruction on certain processors.) The C switch is meant to be a fast decision maker whose capabilities are unachievable any other way in the language.
The SQR Evaluate is really just a convenient way of writing a series of ‘if’ statements. C’s macro facility is not quite up to imitating SQR’s Evaluate, but I might be able to conjure something up using C++ templates plus macros.
The stunning thing to me (as an SQR ignoramus 🙂 ) is that SQR doesn’t let you use an expression as the test value. That makes me think the internal implementation of this is crude. It might be a sort of macro expansion itself, creating code like “if value < 1″…”if value = 2”, etc. and then the designers said “oops… if we let SQR programmers use an expression instead of a value, then the expression will get evaluated multiple times, making things slow or, even worse, generating side effects multiple times with each evaluation.”
You bring up so many good points, I hardy know where to begin.
The C switch and the SQR evaluate may serve different purposes, just as the C language (sometimes called a high level assembler) and the SQR language (perhaps an interpreter of a streamlined version of COBOL with enhanced output) serve different purposes. The switch command might speed up a program while the evaluate command might untangle the source code. I decided to compare them because the SQR Language Reference minimized their differences.
If it stunned you to learn that SQR won’t take an expression as the test value, brace yourself. SQR’s nouns (data objects) and verbs (commands) are far from orthogonal. Many commands take scalar values (literals and variables) but not array elements. Some commands take literals but not variables. And very few commands take expressions. I’ll have to research it, but I think expressions are only allowed in the let command.
Expressions are also allowed in IF and WHILE, but I think that might be it. These and LET sort of remind me of old BASIC. You mentioned COBOL. Then there are even hints of C in that two commands (EXTRACT and FIND) use string offsets that start at 0 instead of 1.
I forgot if and while! I’m blushing. Expressions can also be in where clauses of SQL statements (delete, select, update) and on the right side of a set within an update SQL statement.
Of course, expressions in WHERE and SET clauses of SQL statements are SQL expressions, not SQR expressions (and the difference is quite important)….
I have never seen manuals from SQR v1 days or anything, but I have always assumed that the first few versions of SQR did not support expressions at all. Instead, you had only the “simple” commands: ADD, SUBTRACT, EXTRACT, GET, PUT, etc.
Later, it became clear how much a pain it was to do anything at all complicated using only those commands, so the LET command was added to the language, and along with it support for expressions — but it was “too late” for those expressions to be allowed in the general spots that other languages would allow them (e.g. my personal “favorite”, as arguments to procedure calls)….
(It seems to me that this “theory of SQR evolution” also explains why there is such obvious duplication between the “simple” commands and the functionality available from expressions….)
Similarly, I suspect that support for arrays was added after the language was already established, and in general the support was added by creating new language features rather than by trying to retrofit support to old language features that hadn’t been designed to work with arrays originally.
I think expressions in WHERE and SET can be evaluated by SQL or SQR, depending on their content. For example,
WHERE SUBSTR(DEPTID, 1, 2) = ’50’
OR DEPTID LIKE SETID || ‘%’
I would guess the substring function and concatenation are SQL expressions.
WHERE DEPTID LIKE $dept_prefix || ‘%’
I would guess that SQR evaluates “$dept_prefix || ‘%'” and sends it to the database as a bind variable.
I suspect your theory of SQR evolution is correct, and I wonder about the decision process that led to the apparent practice of freezing old commands, even in the light of new data types.
I suppose SQR for Peoplesoft could be different (though it seems unlikely), but I’m sure that “Hyperion SQR” does not attempt to parse the text of SQL expressions.
For example, I made a symple SQR using contrived BEGIN-SELECTs using the expressions of the style you mentioned above, then ran it with “-s”. Here are the results:
$ sqr sql_expressions_test.sqr -s
SQR V6.2
Copyright (C) Brio Technology 1994-2001. All Worldwide Rights Reserved.
Cursor Status:
Cursor #1:
SQL = SELECT ‘x’ from dual where substr(dummy,1,2)=’50’ or dummy like
dummy || ‘%’
Compiles = 1
Executes = 1
Rows = 1
Cursor #2:
SQL = SELECT ‘y’ from dual where dummy like :1 || ‘%’
Compiles = 1
Executes = 1
Rows = 0
SQR: End of Run.
Note that the limit of SQRs parsing is to look for SQR variables (i.e. $ or # punctuation marks) and convert those individual tokens in the expression to bind variables…..
I don’t have immediate access to tables with DEPTID and SETID columns, but I think if you repeated the test usuing your original examples in that environment, you would see the same results.
I could list some other evidence of various sorts to support my claim; let me know if you would like further convincing…. 🙂
Oops, you’re right. I tried some expressions with an SQR function that didn’t exist in SQL (cond(), edit()). They failed. And the cursor report showed that SQR replaced variables with bind variables, but passed the expressions to SQL to interpret.
I guess I’ve always used functions and operators (e.g. substr() and ||) that exist in both languages. Thanks, I’ve learned something today.
Yes, a good example of why the expression-type distinction is important came up when we were running SQR against Sybase a long time ago. At that point, Sybase only supported “+” as the string concatenation operator, so we had to use “+” in SQL expressions and “||” in SQR expressions.
Another fun point is the different behaviors of the “translate(source,from_set,to_set)” function between Oracle SQL and SQR. If the value of “from_set” is longer than the value of “to_set”, Oracle will delete any characters from “source” that are in the unmatched characters at the end of “from_set”, while SQR will just ignore them….
SOLUTION FOR THE BRAIN TEASER
BEGIN-SELECT
A.ERNCD
A.EFFDT
from PS_EARNINGS_TBL A
WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_EARNINGS_TBL A_ED WHERE A.ERNCD=A_ED.ERNCD AND A_ED.EFFDTSYSDATE)
AND NOT EXISTS (SELECT ‘X’ FROM PS_EARNINGS_TBL WHERE ERNCD = A1.ERNCD AND EFFDT <= SYSDATE)
END-SELECT
I think the comment editor deleted most of your code. Sorry about that – it happens to me too. I hope you’ve still got it in a text file. Try again, and if it still doesn’t work, email it to me at steven@peoplesoftsqr.com.