INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Case question

Case question

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

CODE

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?

CODE

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:

CODE

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

and I tried:

CODE

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:

CODE

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 result1, but that's ugly coding. sad

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

Have fun.

---- Andy

RE: Case question

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

RE: Case question

(OP)
I thought I covered all my tries and failures, but....

WHEN IN (1, 3, 7) THEN result1


gives me 'missing expression' error sad

Have fun.

---- Andy

RE: Case question

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:

CODE

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

RE: Case question

(OP)
Thank you karluk, that did the trick smile

Have fun.

---- Andy

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close