CODEWITHSUNDEEP
sqloracle-databasecase
somagrave
somagrave

Reputation: 47

PL/SQL returning a list in a CASE statement

I need a CASE statement to return a list of strings but I'm having some syntax problems. The resulting SQL should be something like this:

SELECT * FROM FRUIT WHERE COLOR IN ('RED', 'YELLOW')

What I'm trying to do (this doesn't work):

SELECT * FROM FRUIT WHERE COLOR IN
CASE
    WHEN TYPE = *something*
    THEN ('RED', 'YELLOW')
    ELSE ('GREEN')
END

This works:

SELECT * FROM FRUIT WHERE COLOR IN
CASE
    WHEN TYPE = 1
    THEN 'RED'
    ELSE 'GREEN'
END

The error I'm getting:

ORA-00907 missing right parenthesis

It works when I use multiple COLOR = 'X' OR COLOR = 'Y' but I was wondering if there was a way to use only one CASE statement for that. Thanks in advance.

Upvotes: 1

Views: 3057

Answers (1)

Vecchiasignora
Vecchiasignora

Reputation: 1315

A Case statement can only return one value. I think you don't need CASE statement , you can use IN operation instead of Case, like this

SELECT * 
FROM FRUIT 
WHERE TYPE = 'something'  AND COLOR IN ('RED', 'YELLOW') 
OR TYPE <> 'something'  AND COLOR IN ('GREEN')

Upvotes: 5

Related Questions