CASE Statement

Hello All,

I have a condition for which I am trying to write a case statement as follows..

SELECT @.Segment_Field = 'Acct_Status_' + CASE @.Public_record_Type
WHEN NULL THEN ' '
WHEN 'BP' THEN 'BP' + ((CASE @.Bankruptcy_Type WHEN NULL THEN ' ' ELSE '1' END) OR (CASE @.Acct_Status WHEN NULL THEN '' ELSE '2' END))
END

But this is not working.The condition is actually tht I have to set the value for the variable @.Segment_Field acc to the value of variables @.Bankruptcy_Type and @.Acct_Status. It should first check for @.Bankruptcy_Type and if it is not null then the value should be Acct_Status_BP1.Then it should check for @.Acct_Status and if it is not sull then the vlaue should be set to Acct_Status_BP2.

Can somebody please help..

Thanks

The problem is the null comparison. NULL <> NULL. You are trying to compare NULL = NULL, both being UNKNOWN values then it is unknown if they match. SQL Server comparisons are based on TRUE only, all others do not match.

declare @.value varchar(10)
--set @.value = 'Fred'
set @.value = NULL

select case @.value
when 'Fred' then 'It''s Fred'
when 'Barney' then 'It''s Barney'
when NULL then 'It''s NULL'
else 'None of the above' end as whatIsIt

Just rewrite to a searched case expression:

declare @.value varchar(10)
--set @.value = 'Barney'
set @.value = NULL

select case when @.value = 'Fred' then 'It''s Fred'
when @.value = 'Barney' then 'It''s Barney'
when @.value is NULL then 'It''s NULL'
else 'None of the above' end as whatIsIt

Note also that technically CASE is an expression, not a statement. Not that you used the CASE expression wrong, but it helps to remember that it evaluates to a scalar, not control of flow statements like in many other languages.

|||

Thanks Louis.

But my prob is that I have to check for value of two different variables and not one as you specified @.Value..I can definitley check for two different values of one variable but assigning value to a variable on basis of two different variables is what I am not able to do..

|||

Is this what you want? You can put as complex a condition in a WHEN clause as you need, even including subqueries...

SELECT @.Segment_Field = 'Acct_Status_' +

CASE when @.Public_record_Type is null then ''
when @.Public_record = 'BP' THEN 'BP' +
CASE when @.Bankruptcy_Type is null THEN ' ' ELSE '1' END +
CASE when @.Acct_Status is NOT NULL and @.Bankruptcy_Type is null THEN '2' ELSE '' END