CODEWITHSUNDEEP
sqlsql-server
Shark50521
Shark50521

Reputation: 13

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.

Upvotes: 0

Views: 4093

Answers (2)

shawnt00
shawnt00

Reputation: 17915

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.

Upvotes: 1

Xavier J
Xavier J

Reputation: 4624

You need to move the null comparisons to the beginning of the CASE statement. Your second approach is more suited. You should do (as makes sense) a check for [Column X] IS NULL, then [Column Y] IS NULL, then both (with an AND clause).

Upvotes: 0

Related Questions