Comparing Expression Lists (not tuples)

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.

Advertisements

7 thoughts on “Comparing Expression Lists (not tuples)

    • 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.

    • 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s