CODEWITHSUNDEEP
sqloracle-databaseconditional-statementswhere-clause
serefbilge
serefbilge

Reputation: 1714

oracle conditional where clause with filter

I have to use a conditional where clause like below one, what is the working version of this clause?

AND CASE WHEN b.kurum_turu = 1 
         THEN u.kod  in ('1125', '2975', '1127', '4460', '1128', '1126')  
    ELSE u.kod  in ('1125', '2975')                 
END

Upvotes: 4

Views: 13571

Answers (5)

My-Name-Is
My-Name-Is

Reputation: 4942

Transform a list of strings by using CONNECT BY in order to obtain a conditional IN statement.

This solution is usefull if you have serveral conditionals since you can write the IN clause as a single expression without any other AND, OR restrictions.

WHERE u.kod IN 
(
  SELECT rowdata FROM
  ( WITH DATA AS 
    (SELECT CASE WHEN b.kurum_turu = 1 THEN '1125, 2975, 1127, 4460, 1128, 1126' ELSE '1125, 2975' END rowdata FROM dual )
    SELECT trim(regexp_substr(rowdata, '[^,]+', 1, LEVEL)) rowdata FROM DATA CONNECT BY instr(rowdata, ',', 1, LEVEL - 1) > 0
  )
)

This comes with the drawback that the transformation of the comma separated values to data rows is a bit complex ... but you can extract this complex part into a well named function in order to hide this complexity.

Upvotes: 0

gaston guido
gaston guido

Reputation: 1

My aproach is this:

 AND 1 = CASE
         WHEN  b.kurum_turu = 1 AND u.kod  in ('1125', '2975', '1127', '4460', '1128', '1126') THEN 1 ELSE 1 END

Upvotes: 0

Vikdor
Vikdor

Reputation: 24124

You can rewrite that as

...
WHERE
    (
        (b.kurum_turu = 1 AND u.kod in ('1125', '2975', '1127', '4460', '1128', '1126'))
        OR
        ((b.kurum_turu IS NULL OR b.kurum_turu != 1) AND u.kod in ('1125', '2975'))
    )

Upvotes: 2

GKV
GKV

Reputation: 501

i think this is what you need..

 where ...AND CASE WHEN b.kurum_turu = 1 
         and u.kod  in ('1125', '2975', '1127', '4460', '1128', '1126') then 1  
         when  u.kod  in ('1125', '2975') then 1
     else 0      
END=1

Upvotes: 2

Taryn
Taryn

Reputation: 247680

Sounds like you want this for a WHERE clause:

WHERE
(
  b.kurum_turu = 1
  AND u.kod  in ('1125', '2975', '1127', '4460', '1128', '1126')
)
OR
(
  u.kod  in ('1125', '2975') 
)

Upvotes: 10

Related Questions