CODEWITHSUNDEEP
sqloracle-databasecasedecode
user1252398
user1252398

Reputation: 1149

How many WHEN....THEN Condition is allow in CASE in SQL

I am working with the CASE & DECODE. The SYNTAX of CASE is : 

CASE  [ expression ]
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  WHEN condition_n THEN result_n
  ELSE result
END

In CASE if we write any condition we can write it by using WHEN...THEN.

SO, Could you tell me how many number of times we can write WHEN... THEN condition in one CASE. is their any limit to write WHEN....THEN Condition in one CASE.

Thanks for your reply. Actually I got the answer of my question. I got the answer of My question.

Actually we can write only 255 comparisons in one CASE. Each WHEN...THEN clause is considered 2 comparisons.

we can write 255 comparisons in one CASE statement. Each WHEN...THEN clause is considered 2 comparisons.

SAME LIMIT IS APPLIED TO DECODE.

The maximum number of arguments in DECODE function is 255. This includes : EXPRESSION,SEARCH and RESULT arguments.

Upvotes: 0

Views: 8131

Answers (2)

user330315
user330315

Reputation:

Right from the 11.2 manual at http://docs.oracle.com/cd/E11882_01/server.112/e41084/expressions004.htm#SQLRF20037

The maximum number of arguments in a CASE expression is 65535. All expressions count toward this limit, including the initial expression of a simple CASE expression and the optional ELSE expression. Each WHEN ... THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the return_expr itself is a CASE expression

(Emphasis mine)

Edit
Apparently this limit has been significantly increased in 11.2 because in the 11.1 and the 10.2 manual the limit is documented as 255

http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm#i1033392
http://docs.oracle.com/cd/B28359_01/server.111/b28286/expressions004.htm#i1033392

Upvotes: 7

YvesR
YvesR

Reputation: 6232

There is no limit on this, you can do as much as you want... but what is your goal / approach? I think you should solve such a big case when issue with a model.

Upvotes: -1

Related Questions