April 24, 2006 at 8:58 am
I read BOL for CASE function (SQL Server Scriptings). But it seems the information is mentioned short; also CASE examples in BOL are not many.
I'd like to know all other possible/flexible usages of CASE in reality and practical real life SQL scriptings. For example, in BOL, CASE use combined with SELECT statements. Some experienced scripters say it can be used combined with WHERE clause as well.
Thanks in advance.
April 24, 2006 at 10:03 am
Here's a comprehensive article on the numerous uses of case
**ASCII stupid question, get a stupid ANSI !!!**
April 24, 2006 at 1:51 pm
Try the link below for almost everything you need to do with CASE statement. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
April 25, 2006 at 6:25 am
CASE is quite helpful and I use it frequently. Think of it as a "categorization" tool, creating groups as it interprets data. Some examples:
...
CASE
WHEN (i.status & 2048) > 0 THEN 'PRIMARY KEY'
WHEN (i.status & (2|4096)) > 0 THEN 'UNIQUE'
ELSE '' END AS UniqueType,
...
SUM(CASE SStatus WHEN 'Delivered' THEN b.NoPkgs END) AS NoDelv,
SUM(CASE SStatus WHEN 'Delivered Early' THEN b.NoPkgs END) AS NoEarly,
CASE is almost always useful when you have multiple data elements (columns) to test that don't neatly fit into order. In other words, it solves logic problems like:
...
CASE WHEN a.Sender_ZipCode IS NULL OR a.Sender_ZipCode = ''
THEN Accts.zipcode
ELSE a.Sender_ZipCode
CASE is fantastic when you want to translate data codes into more readable report output, like:
(CASE WHEN F2.ChargeCode IS NULL THEN 'NO' ELSE 'YES' END) AS Residential,
CASE is also useful for converting data:
(CASE WHEN Billed_Weight LIKE 'LTR' THEN 0 ELSE CAST(Billed_Weight AS numeric) END)
All of these examples are 'real world'.
Mike Prascak
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply