This post is a long-winded answer to a question Bryn Llewellyn asked me on Twitter about comparing expression lists.
Sabine Heimsath recently tweeted a regret about CASE and “tuples”, and gave an example of what she meant.
This is an example of a “simple” case statement (or expression?) using expression lists, not tuples. Tuples are unordered sets of attribute values, and here order matters. As Sabine discovered, this syntax is not supported.
[Update 2017-05-09: a case statement ends with
END CASE and has statements after each
THEN keyword. A case expression has expressions after
THEN but does not have the
CASE keyword at the end. Sabine appears to have mixed the two syntaxes.]
Oren Nakdimon then gave an example of a “searched” case
statement expression that handles expression lists:
Sabine thought this meant that IN works and “=” does not. I replied that “=” works, but only in SQL not in PL/SQL, and provided you put an extra pair of parentheses around the expression list on the right hand side.
This led to a question from Bryn Llewellyn:
Since I need more than 140 characters, here is my attempt to answer Bryn.
His example is a simple comparison condition. If you look at the illustration below (taken from the documentation), Bryn is using the lower form with parentheses around the left-hand expressions. Therefore, the expression_list must also be surrounded by parentheses.
Additionally, since there are multiple expressions to the left of the operator, the expression_list must also be in its lower form:
So there must be an additional set of parentheses.
All of the above are from the SQL reference. The PL/SQL language reference has its own documentation on comparisons, and I have found no explicit reference to expression lists.