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
