Home » SQL & PL/SQL » SQL & PL/SQL » DECODE and CASE Expression Limitation (merged 3)
DECODE and CASE Expression Limitation (merged 3) [message #402162] Thu, 07 May 2009 23:07 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
In a simple CASE expression, the Oracle Server searches for the first WHEN ... THEN pair for which pair is equal to comparison expr and returns return_expr.
If none of the WHEN ... THEN pairs meet this comparison, and an ELSE clause exists, then Oracle returns else_expr
If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL.
Literal NULL cannot be specified for all the return_exprs and the else_expr

According to the below example, Literal NULL can be specified for all the return_exprs and the else_expr. Please confirm is that correct or what is Literal NULL means?
SQL>  select
  2  CASE
  3    WHEN 1 < 2 THEN NULL
  4    WHEN 3 < 4 THEN NULL
  5    ELSE NULL
  6  END
  7  from dual;

C
-


[Updated on: Thu, 07 May 2009 23:13]

Report message to a moderator

Oracle 9i SQL CASE Expression Limitation [message #402165 is a reply to message #402162] Thu, 07 May 2009 23:17 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Limited to 255 comparisons in CASE statement. Each WHEN ... THEN pair is considered 2 comparisons. To avoid exceeding the limit of 128 choices, you can nest CASE expressions. That is return_expr can itself be a CASE expression.

What is 128 choices?
Re: CASE Expression Limitation (merged) [message #402194 is a reply to message #402162] Fri, 08 May 2009 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Literal NULL cannot be specified for all the return_exprs and the else_expr

Where did you see that? Is this your opinion or is this in the documentation? In the latter, post a link to it.

Regards
Michel
Re: Oracle 9i SQL CASE Expression Limitation [message #402195 is a reply to message #402165] Fri, 08 May 2009 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ygsunilkumar wrote on Fri, 08 May 2009 06:17
Limited to 255 comparisons in CASE statement. Each WHEN ... THEN pair is considered 2 comparisons. To avoid exceeding the limit of 128 choices, you can nest CASE expressions. That is return_expr can itself be a CASE expression.

What is 128 choices?

Where did you see that? Is this your opinion or is this in the documentation? In the latter, post a link to it.

Regards
Michel

Re: Oracle 9i SQL CASE Expression Limitation [message #402203 is a reply to message #402195] Fri, 08 May 2009 01:31 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Is not my opinion, I read it in the below links.

http://www.itk.ilstu.edu/docs/Oracle/server.101/b10759/expressions004.htm

http://mis3nt.gsnu.ac.kr/PublicData/Oracle11gDoc/server.111/b28286/expressions004.htm
Re: CASE Expression Limitation (merged) [message #402205 is a reply to message #402162] Fri, 08 May 2009 01:36 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Is not my opinion, I read it in the below links.
Are you bragging or complaining?

Nobody owes you any response.
Re: CASE Expression Limitation (merged) [message #402207 is a reply to message #402205] Fri, 08 May 2009 01:40 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Question was asked by Mical saying Where did you see that? Is this your opinion or is this in the documentation? In the latter, post a link to it.
So for that I replied. Who will reply it other than me because question is asked to me right?
Re: CASE Expression Limitation (merged) [message #402211 is a reply to message #402205] Fri, 08 May 2009 01:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
BlackSwan wrote on Fri, 08 May 2009 08:36
>Is not my opinion, I read it in the below links.
Are you bragging or complaining?

Nobody owes you any response.

Then why don't you simply NOT respond?
Is it really your goal to leave an insult in each and every thread?

[Edit: Typo]

[Updated on: Fri, 08 May 2009 01:56]

Report message to a moderator

Re: CASE Expression Limitation (merged) [message #402221 is a reply to message #402211] Fri, 08 May 2009 02:22 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Frank wrote on Fri, 08 May 2009 01:56
BlackSwan wrote on Fri, 08 May 2009 08:36
>Is not my opinion, I read it in the below links.
Are you bragging or complaining?

Nobody owes you any response.

Then why don't you simply NOT respond?
Is it really your goal to leave an insult in each and every thread?

[Edit: Typo]


My intension is not to insult anybody. Please donot mistake me. I replied to the question thats it.
Re: CASE Expression Limitation (merged) [message #402227 is a reply to message #402221] Fri, 08 May 2009 02:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ygsunilkumar wrote on Fri, 08 May 2009 09:22

My intension is not to insult anybody. Please donot mistake me. I replied to the question thats it.


Isn't it clear, both from what I quoted and from the message I replied to (see top of posting: "message is a reply to") that I was NOT referring to you?!
Re: CASE Expression Limitation (merged) [message #402228 is a reply to message #402227] Fri, 08 May 2009 02:31 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Thanks I got it.
Oracle 9i SQL DECODE Function Limitation [message #402229 is a reply to message #402162] Fri, 08 May 2009 02:34 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Limited to 255 comparisons in CASE statement. Each WHEN ... THEN pair is considered 2 comparisons. To avoid exceeding the limit of 128 choices, you can nest CASE expressions. That is return_expr can itself be a CASE expression

The maximum number of components that you can have in a decode function is 255. This includes the expression, search, and result arguments.

In the above two statements, for CASE Statements there is solution that CASE can be nested to avoid exceeding the limit of 128 choices.

Is there any solution for DECODE Function to avoid exceeding the limit of 255? Thanks in advance
Re: Oracle 9i SQL DECODE Function Limitation [message #402231 is a reply to message #402229] Fri, 08 May 2009 02:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why don't you explain your actual problem?
We might be able to help you in ways you never imagined. In my opinion a case with more than 128 options is a flaw.
Re: Oracle 9i SQL DECODE Function Limitation [message #402237 is a reply to message #402231] Fri, 08 May 2009 03:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
This approach does indeed look suboptimal, to say the least.

Have you considered a lookup table?

MHE
Re: CASE Expression Limitation (merged) [message #402242 is a reply to message #402228] Fri, 08 May 2009 03:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@ygsunilkumar - this would appear to be a mistake in the documentation. It's there in the offical 10.2 and 11.1 documentation and you do seem to be able to specify nulls for all values of a simple case expression.
Re: DECODE and CASE Expression Limitation (merged 3) [message #402537 is a reply to message #402162] Mon, 11 May 2009 07:25 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I splitted the posts that were not related to this topic and put in a new topic named "On posts, names and avatars" in Suggestion & Feedback forum.

Regards
Michel

[Updated on: Mon, 11 May 2009 07:26]

Report message to a moderator

Previous Topic: BULK INSERT
Next Topic: Materialized view creation.
Goto Forum:
  


Current Time: Thu Dec 08 03:59:50 CST 2016

Total time taken to generate the page: 0.09027 seconds