Multiple Case End 2

[MissyEd]

Im trying to create a string in a select statement that returns which columns are in error e.g

SELECT Col1, Col2, Col3,
CASE WHEN Col1 = 0 THEN 'Col1 Is In Error' END +
CASE WHEN Col1 = 0 THEN 'Col1 Is In Error' END +
CASE WHEN Col1 = 0 THEN 'Col1 Is In Error' END AS Result
FROM Table1

It doesnt work, so wondering what else I can try.

Life is a blast when you have a semi-automatic..

 

[john76]

Try wrapping your case statements inside of coalesce functions with the alternate path of '' or ' '.

Sample as follows works:

Declare @Col1 int,
        @Col2 int,
        @Col3 int

Set @Col1 = 0
Set @Col2 = 0
Set @Col3 = 3

Select  Col1 = @Col1,
        Col2 = @Col2,
        Col3 = @Col3,
        ColString = Coalesce(Case When @Col1 = 0 Then 'Col1 is 0 ' End, ' ') +
                    Coalesce(Case When @Col2 = 0 Then 'Col2 is 0 ' End, ' ') +
                    Coalesce(Case When @Col3 = 0 Then 'Col3 is 0 ' End, ' ')
Go

HTH,
John

 

[DanEvans]

The problem is the nulls. If you add an "Else" statment, then you will get a result:
SELECT Col1, Col2, Col3,

CASE WHEN Col1 = 0 THEN 'Col1 Is In Error' else '' END +
CASE WHEN Col2 = 0 THEN 'Col2 Is In Error' else '' END +
CASE WHEN Col3 = 0 THEN 'Col3 Is In Error' else '' END AS Result
FROM Table1


Thanks,
Dan

 

[MissyEd]

Thanks John

Also got this code off a colleague - not sure which is the better option, guess I'll have to test em.

SELECT Col1, Col2, Col3,
CASE WHEN 1 = 1
CASE WHEN Col1 = 0 THEN 'Col1 Is In Error' ELSE '' END +
CASE WHEN Col1 = 0 THEN 'Col1 Is In Error' ELSE '' END +
CASE WHEN Col1 = 0 THEN 'Col1 Is In Error' ELSE '' END
END AS Result
FROM Table1


Life is a blast when you have a semi-automatic..

 

[MissyEd]

Thx Dan - easiest soln for me as its a minor amend - can't beleive I didnt realise it was a NULL problem *doh* end of day and all that. Many thanks for the replies.

Life is a blast when you have a semi-automatic..