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.
Below is a process that I follow every year to create a partition in existing multi-dimensional SSAS cubes that I maintain. Log in to t...
This morning I was trying to call a stored procedure and insert the results in a temp table using the following code -- create table #...
Did you ever have a situation where you felt like converting a Sql Server Analysis Services in Multidimensional Mode to Tabular Mode ? ...
I was trying to build an SSIS package that one of my colleagues has created. The first error that was thrown was as below: Exception des...