How to return multiple values from a SQL Case subquery without grouping

Question

I have the following SQL query where the CASE statement needs to return multiple values.

select TASK_NAME 
from TASK 
where TASK_TYPE in (          
    case 
        when (select USER_COUNTRY from USER) in (select COUNTRY_NAME from COUNTRY where CONTINENT = 'Europe') then ('Adhoc', 'Resell')
        when (select USER_COUNTRY from USER) in (select COUNTRY_NAME from COUNTRY where CONTINENT = 'Americas') then ('Classified', 'Bought', 'Handle')
        else ('None', 'Blank')
    end )

However, the number of WHEN clauses is not fixed as there may be more conditions. I don't think SQL return multiple values from CASE statement will work.

I should also add that grouping is not recognised by the application which this query is passed to.

I apologise for not providing the tables. I will do so in due course. Just wanted to get this out there.

What do you mean by "needs to return multiple values?" Case statements by design function in the following manner - the first condition to be true will be returned. It sounds like you need a join, not a CASE. — rwking
– rwking, Commented Jan 28, 2015 at 15:41

Gordon Linoff · Accepted Answer · 2015-01-28 15:41:59Z

2

You can move the comparison to the when clauses and have the case return a scalar value indicating a match:

where 1 = (case when (select USER_COUNTRY from USER) in (select COUNTRY_NAME from COUNTRY where CONTINENT = 'Europe') and Task_Type IN ('Adhoc', 'Resell')
                then 1
                when (select USER_COUNTRY from USER) in (select COUNTRY_NAME from COUNTRY where CONTINENT = 'Americas') and Task_Type IN ('Classified', 'Bought', 'Handle')
                then 1
                when Task_Type IN ('None', 'Blank')
                then 1
            end )

This is a rather strange set of conditions. Are you sure you can't express the logic better using JOIN.

answered Jan 28, 2015 at 15:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Confounder Over a year ago

Unfortunately, the use of groups and joins are limited in the application. In fact. the query in my original post is a subquery.

Gordon Linoff Over a year ago

@Confounder . . . My actual concern is that USER might have more than one row. In that case, the SQL would typically generate an error.

Collectives™ on Stack Overflow

How to return multiple values from a SQL Case subquery without grouping

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related