Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Maximum Number of "When Then" lines in a CASE statement? Expand / Collapse
Author
Message
Posted Saturday, May 31, 2008 12:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 4:38 AM
Points: 133, Visits: 308
Does anyone know if there's a maximum number of "When xxx THEN yyy" lines you can have within a single CASE statement?

Thanks.

Roger
Post #509492
Posted Saturday, May 31, 2008 1:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 7,152, Visits: 15,634
I can't say I know, but at the risk of sounding pedantic - if you have to ask the question, you should be considering a lookup table.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #509500
Posted Saturday, May 31, 2008 1:23 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:19 PM
Points: 4,360, Visits: 9,543
I don't know if there is a limit - at least, I cannot find anything that says there is. However, when I find that I have a very large statement I start to look at other ways to perform this operation.

For example, if I am converting some internal value/code to a human readable value - I will create a table instead and use the table.

I guess it really depends on what you are trying to do.

Jeff


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #509501
Posted Saturday, May 31, 2008 2:20 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
I know it does not provide any value (as already pointed out, a lookup table should be used), but I get an overflow with 9712 WHEN cases.
I just had to try this out
SELECT CASE 'Hello'
WHEN '' THEN ''
.... 9710 more
WHEN '' THEN ''
END

But you can add more cases within these like
SELECT CASE 'Hello'
WHEN '' THEN
CASE 'Hello2'
WHEN '' THEN ''
.... x more
WHEN '' THEN ''
.... 9710 more
WHEN '' THEN ''
END


Now it's up to you to derive some value from this exercise


Best Regards,
Chris Büttner
Post #509504
Posted Saturday, May 31, 2008 2:21 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:23 AM
Points: 2,281, Visits: 4,233
Based on a test, 512 WHEN are accepted.

SQL = Scarcely Qualifies as a Language
Post #509505
Posted Saturday, May 31, 2008 3:46 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
Matt Miller (5/31/2008)
I can't say I know, but at the risk of sounding pedantic - if you ask to ask the question, you should be considering a lookup table.....


Aye. Just because you can do something doesn't mean it's a great idea.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #509522
Posted Thursday, November 21, 2013 1:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 2:23 PM
Points: 2, Visits: 1
Hey, I appreciate the test. I have a list of thousands of entries (people have a habit of typing the same thing very different ways) that I need to examine and then categorize. Initially I was looking at a monster CASE using LIKE to collapse the list. I guess it depends how much commonality there is to be found.
Post #1516579
Posted Thursday, November 21, 2013 2:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 12,921, Visits: 32,285
terrencepierce (11/21/2013)
Hey, I appreciate the test. I have a list of thousands of entries (people have a habit of typing the same thing very different ways) that I need to examine and then categorize. Initially I was looking at a monster CASE using LIKE to collapse the list. I guess it depends how much commonality there is to be found.


yeah definitely sounds like using a lookup table would be better;

then you can join on the "WHEN" entry value to the lookup table, and get the translated "THEN" value from the table.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1516581
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse