CODEWITHSUNDEEP
sqloracle-databaseselect
Denys
Denys

Reputation: 4557

Case when using IN clause

I need a query to return a certain result for a certain column depending on what value the column that is being run the select statement against has.

if the column is one of the following : I, D, U then I want to return Y

if the column is one of the following : N, E, D then I want to return N

                      else : I want to return NULL

I wrote the following statement but it doesn't work.

   SELECT HIERARCHY_TYPE,
          NODE_ID,
          NODE_TYPE,
          NODE_NAME,
          NODE_LEVEL,
          PREFERRED_ALIAS,
          PARENT_NODE_ID,
          CASE ACTIVE_INDICATOR 
             WHEN ('I' or 'U' or 'Y') THEN 'Y'
             WHEN ('D' or 'E' or 'N') THEN 'N'
             ELSE NULL
          END
     FROM MV_HIERARCHY MV;

Is there a way to rewrite it without using multiple OR clauses for each possible value?

Upvotes: 1

Views: 7160

Answers (3)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23727

decode(mod(nullif(instr('IDUEYN',active_indicator),0),2),0,'N',1,'Y')

Upvotes: 2

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

CASE 
   WHEN ACTIVE_INDICATOR IN ('I','U','Y') THEN 'Y'
   WHEN ACTIVE_INDICATOR IN ('D', 'E', 'N') THEN 'N'
   ELSE NULL -- useless, but for readbility
END as ACTIVE_INDICATOR

You've got to repeat ACTIVE_INDICATOR, cause I don't think (may be wrong) you can use the syntax

CASE <field>
   WHEN IN()

but you can use

CASE
  WHEN <field> IN()

Upvotes: 2

mjsqu
mjsqu

Reputation: 5417

I'd use the IN operator:

SELECT HIERARCHY_TYPE,
NODE_ID,
NODE_TYPE,
NODE_NAME,
NODE_LEVEL,
PREFERRED_ALIAS,
PARENT_NODE_ID,
CASE 
  WHEN ACTIVE_INDICATOR IN ('I','U','Y') THEN 'Y'
  WHEN ACTIVE_INDICATOR IN ('D','E','N') THEN 'N'
ELSE NULL
END AS ACTIVE_INDICATOR
FROM MV_HIERARCHY MV;

Upvotes: 3

Related Questions