Shark50521 Shark50521 - 3 months ago 8
SQL Question

Case expression with two columns

I have a case statement in SQL Server 2008 that uses the following syntax

case
when [Phone1] = 'Cell' then [CellNumber]
when [Phone1] = 'Home' then [HomeNumber]
when [Phone1] = 'Work' then [WorkNumber]
when [Phone2] = 'Cell' then [CellNumber2]
when [Phone2] = 'Home' then [HomeNumber2]
when [Phone2] = 'Work' then [WorkNumber2]
else [CellNumber]
end as [PrimaryPhone]


The statement itself runs fine, however it doesn't ever use my else statement. If I put else 'TextExample', it will put that in my results but I cannot get it to output Column1 in the result of a null value.

I have also tried the following to no avail.

case
when [ColumnX] = 'Value1' then [Column1]
when [ColumnX] = 'Value2' then [Column2]
when [ColumnX] = 'Value3' then [Column3]
when [ColumnY] = 'Value1' then [Column4]
when [ColumnY] = 'Value2' then [Column5]
when [ColumnY] = 'Value3' then [Column6]
when [Column X] is null and [Column Y]is null then [Column1]
else [Column1]
end as [ColumnName]


Here is an example of my
Output from query

I am expecting ColumnName to always be filled in with Column 1-6, however the else is always ignored, I am assuming because one of the above when statements in the searched case is true, but it isn't putting the value there in turn. It is giving me a null value even though one of the above when's was true, and the result should not(I don't think at least) be a null value.

I have edited my first example to have column names/plain english.

Answer
coalesce(case
    when [ColumnX] = 'Value1' then [Column1]
    when [ColumnX] = 'Value2' then [Column2]
    when [ColumnX] = 'Value3' then [Column3]
    when [ColumnY] = 'Value1' then [Column4]
    when [ColumnY] = 'Value2' then [Column5]
    when [ColumnY] = 'Value3' then [Column6]
end, [Column1]) as [ColumnName]

The reason you need the coalesce() is that even though you're selecting one of the six columns based on the values in two other columns you haven't also guaranteed that the corresponding value is not null. The coalesce() says that where the computed value is missing, use Column1 by default. You could extend that this way if necessary:

coalesce(
...
end, [Column1], [Column2], [Column3] ...) as [ColumnName]

Your first attempt looks like it should have worked as long as the flagged value doesn't turn out to be null. It seems likely that this wasn't supposed to happen so perhaps that's really where the problem lies.