CASE WHEN in Where clause and with THEN with more than 1 return value

Asked

Viewed 144 times

1

Hello. I have an application for assembly of queries, in which I pass the parameters in the clause "Where". Only I came across a situation where I need to use CASE and in the THEN clause a range of values (to return in an IN). The idea goes something like this:

select [...]
from table
where campo in 
            case 
                 when <condicao1> then 'Valor1'
                 when <condicao2> then 'Valor1', 'Valor2'
                 else 'Valor3' end

The problem is precisely in condica2, when I provide more than one value, the case syntax does not accept 'Valor1', 'Valor2'. Does anyone have an idea if there’s a way to do it?

sql oracle case

  • Try 1 return between () then (value1) 2 the good is old "if" Where ( (<cond1> and field = 'value1') or (<cond2> and field in ('value1','value2') )

  • I tried this but Oracle does not accept the syntax select * from user_objects Where object_type in (CASE WHEN 1=1 THEN ('TABLE','INDEX') ELSE ('FUNCTION') END) ORA-00907: Missing right parenthesis

2 answers

0

Remember the logical operators, if using and (it will have to validate everything inside), if it is among some of the values use OR. Something like that between:

where ( case when condicao1 then 'Valor1' or when condicao2 between x and x then 'Valor2' )

use the between for your range.

0

I would reformat this query to:

select [...]
from table
where
    (campo = 'Valor1' and <condicao1>)
    or (campo in ('Valor1', 'Valor2') and <condicao2>)
    or campo = 'Valor3'

Browser other questions tagged sql oracle case

You are not signed in. Login or sign up in order to post.