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.

### Like this:

Like Loading...

*Related*

you assume that “1, 2” is an expression, but it isn’t see http://docs.oracle.com/database/122/SQLRF/About-SQL-Expressions.htm#SQLRF52066

No, 1 is an expression and 2 is an expression.

To the left of the operator, the two expressions are separated by a comma and surrounded by parentheses, as in the first illustration, “lower form”.

1,2 is an expression list, “upper form”.

(1,2) is an expression list, “lower form”.

To the right of the operator, the “lower form” of the expression list is required. So (1,2) goes inside another set of parentheses.

Stew, you are right (of course), sorry

good explanation !

Matthias

Stew, you are right (of course), sorry

good explanation !

Matthias

Stew, thanks for this post.

Just a nitpicking remark: the examples Sabine and I gave are for simple and searched case *expressions*, not *statements*.

Thanks,

Oren.

Oren, thanks for pointing that out.

Actually, Sabine used END CASE at the end of her example, which is syntax reserved for the case statement – so her syntax is a mix of expression and statement.

She probably meant expression and you certainly did.

I have corrected the post. Thanks again, Stew

Let’s give Sabine the benefit of the doubt :-)

As she clearly wanted a CASE expression and not a CASE statement, we can treat the last CASE as an alias, like in the following example:

select case 1 when 1 then ‘CASE can be an alias’ end case from dual;

CASE

————————————————————

CASE can be an alias

1 row selected.