CODEWITHSUNDEEP
sqlsqlite
Sh4pe
Sh4pe

Reputation: 1886

Make CASE-WHEN-clause emit a tuple

I have the following query:

SELECT
       m1.ID, 
       (CASE WHEN <condition> THEN
             <expr1.1>
        ELSE
             <expr1.2>
        END) as [Tag1], 
       (CASE WHEN <condition> THEN
             <expr2.1>
        ELSE
             <expr2.2>
        END) as [Tag2], 
       (CASE WHEN <condition> THEN
             <expr3.1>
        ELSE
             <expr3.2>
        END) as [Tag3],
  FROM MyTable AS m1
       LEFT OUTER JOIN MyOtherTable m2
            ON m2.ID = m1.ID

As you can see, I have the same condition three times, but different expressions in each branch. Is it possible in SQL to only write the condition once and let it emit a three-tuple in this case? Or is it in any other way possible to prevent writing the same condition multiple times?

I want to use this in Sqlite3.

Upvotes: 1

Views: 867

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

Sadly (afaik) SQLite does not have common table expressions, but you could calculate the expression in a subquery and use CASE on that instead.

It will not eliminate any case expression, but it can simplify the expression in each and it will only need to be changed in once place;

As an example, the query;

SELECT CASE WHEN sel=1 OR sel=3 AND a<>2 OR sel=2 AND a<>3 OR sel=4 THEN a ELSE b END e,
       CASE WHEN sel=1 OR sel=3 AND a<>2 OR sel=2 AND a<>3 OR sel=4 THEN b ELSE c END f,
       CASE WHEN sel=1 OR sel=3 AND a<>2 OR sel=2 AND a<>3 OR sel=4 THEN c ELSE a END g
FROM olle;

...could be rewritten as...

SELECT CASE WHEN exp THEN a ELSE b END e,
       CASE WHEN exp THEN b ELSE c END f,
       CASE WHEN exp THEN c ELSE a END g
FROM (SELECT *, sel=1 OR sel=3 AND a<>2 OR sel=2 AND a<>3 OR sel=4 exp FROM olle);

This example may not look like it's simplified much, but for a complex expression or one that is changed often it may make quite a lot of difference.

An SQLfiddle to test with.

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

That's basically it.

You can use a CASE statement in a JOIN to a table, that would allow you to get down to one CASE statement, but unless your data is already structured in a way that makes this possible it's probably not worthwhile.

Upvotes: 0

Related Questions