Case question 1

[Andrzejek]

PL/SQL Case question,
simple example from the web:

CASE search_expression
  WHEN expression1 THEN result1
  WHEN expression2 THEN result2
  ...
  WHEN expressionN THEN resultN
  ELSE default_result
END

But what if have want to combine expression1 and expression 2 to give me the same result?

CASE some_number
  WHEN 1, 3, 7 THEN result1
  WHEN 2 THEN result2
  ...
END

My CASE cannot have BETWEEN because it is either 1 or 3 or 7.
I tried:

CASE some_number
  WHEN 1 OR 3 OR 7 THEN result1
  WHEN 2 THEN result2
  ...
END

and I tried:

CASE some_number
  WHEN some_number = 1 OR some_number = 3 OR some_number = 7 THEN result1
  WHEN 2 THEN result2
  ...
END

That all have me en error (Missing keyword)

I know I can do:

CASE some_number
  WHEN 1 THEN result1
  WHEN 3 THEN result1
  WHEN 7 THEN result1

  WHEN 2 THEN result2
  ...
END

But this way I would repeat [tt]result1[/tt], but that's ugly coding. :-(

I cannot find the example of combining several expressions in CASE WHEN statement, that should be possible

Have fun.

---- Andy

 

[carp]

Have you tried
WHEN IN (1, 3, 7) THEN result1
?

 

[Andrzejek]

I thought I covered all my tries and failures, but....[tt]

WHEN IN (1, 3, 7) THEN result1[/tt]

gives me 'missing expression' error :-(


Have fun.

---- Andy

 

[karluk]

I rarely write pl/sql code any more, so I had to consult the documentation to find the correct syntax. It turns out that you are using the syntax for what Oracle calls "simple case expressions", and simple case expressions don't allow Boolean logic. What you need is what Oracle calls "searched case expressions". Your code should work if you eliminate the "some_number" immediately after the CASE key word and plunge immediately into your when clauses. Something like the following:

CASE
  WHEN some_number in (1, 3, 7) THEN result1;
  WHEN some_number = 2 THEN result2;
  ...
END CASE;

 

[Andrzejek]

Thank you karluk, that did the trick


Have fun.

---- Andy