Using IN Operator in Nested Case in PostgreSQL

Question

I need a nested case structure in a query which the outer case uses IN operator. I used same inner case part in all conditions of outer case as shown in the following example:

SELECT
    CASE
        WHEN
            (CASE
                WHEN expression1 THEN value1
                WHEN expression2 THEN value2
                ELSE value3
            END) in (1, 2)
            THEN 'A'
        WHEN
            (CASE
                WHEN expression1 THEN value1
                WHEN expression2 THEN value2
                ELSE value3
            END) in (3, 4)
            THEN 'B'
        ELSE 'C'
    END

That example works well but I want to use it as follows if possible:

SELECT
    CASE 
        (CASE
                WHEN expression1 THEN value1
                WHEN expression2 THEN value2
                ELSE value3
        END)

        WHEN
            an_alias_if_necessary in (1, 2)
            THEN 'A'
        WHEN
            an_alias_if_necessary in (3, 4)
            THEN 'B'
        ELSE 'C'
    END

The following example of that nested case which does not have IN operator works well. Is it possible to use same structure with IN operator ?

SELECT
    CASE 
        (CASE
                WHEN expression1 THEN value1
                WHEN expression2 THEN value2
                ELSE value3
        END)

        WHEN asked_value1 THEN 'A'
        WHEN asked_value2 THEN 'B'
        ELSE 'C'
    END

Laurenz Albe · Accepted Answer · 2018-02-21 12:18:22Z

1

PostgreSQL does not support the SQL standard feature “Comma-separated predicates in simple CASE expression” (F263), so you cannot do that.

You'll have to use a construct like:

CASE CASE
        WHEN expression1 THEN value1
        WHEN expression2 THEN value2
        ELSE value3
     END
   WHEN 1 THEN 'A'
   WHEN 2 THEN 'A'
   WHEN 3 THEN 'B'
   WHEN 4 THEN 'B'
   ELSE 'C'
END

answered Feb 21, 2018 at 12:18

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

wildplasser Over a year ago

You could probably hack your way around it by using ...IN(VALUES(1,2,3)) (maybe needs a SELECT, too)

Emre Külah Over a year ago

@wildplasser Can you give a complete example?

wildplasser Over a year ago

BTW: list of scalars: postgresql.org/docs/10/static/…

Laurenz Albe Over a year ago

@wildplasser I don't understand how these could be used in a CASE expression without repeating the left side expression, which the OP seeks to avoid.

wildplasser Over a year ago

I thought any condition is valid, iff it yields a boolean.Exists(...) does work. Maybe an extra set of() is needed? (working on it)

wildplasser · Accepted Answer · 2018-02-21 13:02:13Z

0

It seems you need to use a VALUES(1,2) imediate table

SELECT
        topic_id
        ,krant_id
        , CASE WHEN (krant_id IN (SELECT val FROM (VALUES(1),(2) ) vals(val)))THEN 'OneTwo' ELSE 'Normal' END AS label
FROM react
WHERE react_date >= '2011-01-01'
AND react_date < '2011-02-01'
        ;

Tucking the VALUES() into a CTE could simplify the query a bit:

WITH vals(val) AS (VALUES(1),(2))
SELECT
        topic_id
        ,krant_id
        , CASE WHEN (krant_id IN (SELECT val FROM vals)) THEN 'OneTwo' ELSE 'Normal' END AS label
FROM react
WHERE react_date >= '2011-01-01'
AND react_date < '2011-02-01'
        ;

Sorry, I used my own existing table. The syntax is correct,though.

answered Feb 21, 2018 at 13:02

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Collectives™ on Stack Overflow

Using IN Operator in Nested Case in PostgreSQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related