I have used case statements before several times but there were some new learnings from the SQL Share video of Andy Warren
Here are my learnings of the case statement summarised:
People often get confused whether to use if else statements or case statements in T-SQL. According to Andy, only case statements can be used in T-SQL statements. If else statements cannot be used in select statements because if else statements can be used in control flow.
Following are some of the situations and syntax that can be used with case statements. The most important is included in the last syntax (Syntax 5)
case columnname when 'exisitng value1' then 'new value1'when 'exisitng value2' then 'new value2'end as aliascolumnname
The drawback with the above syntax is when the conditions of the when statement is not matched the result returned is null in the column.
case columnname when 'exisitng value1' then 'new value1'when 'exisitng value2' then 'new value2'else columnameend as aliascolumnname
When the else condition is used the drawback in Syntax1 is fixed.
If you want to use case statement for multiple columns the syntax is as follows;
case when column1='exisitng value' then 'new value'when column2='exisitng value' then 'new value'else column1end as aliascolumnname
Notice that there is no column name after the case in the above statement.
Nested case statementswhen formatted looks better and you can understand better
case when columnname1 = 'exisitng value' then case when clumnname2 = 'exisitng value1' then 'new value' else columnname1 end else columnname1end as aliascolumnname
The above can also be written in one case statement doing two tests.
Syntax 5 Last but not the least (Very important learning of the day):
You can use case in the order by clause
select columnname from table name order by case when columnname = 'value1' then 0 else 1 end, columnname.
Today I had a situation where a few of the subscriptions for a particular report have failed. I needed a script to run only these subscripti...
I have been in hibernation for too long now. I have changed jobs and now hjaving an opportunity to work on SAS code after nearly 11 years. ...
What is Checksum? Checksum is a function available in sql server 2005 and sql server 2008. It is intended to build a hash index based on an ...