CODEWITHSUNDEEP
sqloracle-databasecasewhere-clause
user2783755
user2783755

Reputation: 588

Oracle - Case in where clause

I am trying to execute a query in Oracle database. The query has case construction in where clause.

where
    sale.op = 2 and
        case when (:stat = -11) then (sale.type_id = 27 or sale.type_id = 28 or sale.type_id = 29)
    else
    (sale.type_id = 27)
    end

But I am getting the following error:

ORA-00907: missing right parenthesis.

In Derby SQL this works. Does anybody can help me? Thank you.

Upvotes: 0

Views: 335

Answers (4)

Fernando Urea
Fernando Urea

Reputation: 11

try this query

where 
     sale.op =2 and
     ((:stat = -11 and sale.type_id=any(27,28,29)) or
       (:stat <> -11 and sale.type_id = 27))

It looks more clear!!!

Upvotes: 1

Hamidreza
Hamidreza

Reputation: 3118

try this query:

where
    sale.op = 2 and
        ((:stat = -11 and (sale.type_id = 27 or sale.type_id = 28 or sale.type_id = 29)) 
       or (:stat <> -11 and sale.type_id = 27))

Upvotes: 1

user330315
user330315

Reputation: 

where sale.op = 2 
and (     (:stat = -11 and sale.type_id in (27, 28, 29))
      or  (:stat <> -11 and sale.type_id = 27)
    )

Upvotes: 5

guigui42
guigui42

Reputation: 2500

You could also try without CASE, using simple operators AND and OR :

where
    sale.op = 2 and ((:stat = -11 and (sale.type_id = 27 or sale.type_id = 28 or sale.type_id = 29))  
        OR (:stat <> -11 and sale.type_id = 27))

Upvotes: 1

Related Questions