February 28, 2014 at 8:15 am
select 'test message' + case when a=0 then 'message 1'
when a<>0 then 'message 2'
else 'XXXXX' end as SQLCOLUMN
IF a=o or 1, then message is
test message message 1
test message message 2
If a is other than 0 or 1, then in ELSE i need to comment out the code like,
--test message
Can we do like this?
February 28, 2014 at 8:28 am
If you want the option to have 'XXXXX' as a complete textstring instead of this added after the intial string, then you need to put the initial string inside the case statement.
-- result will always start with string "test message: "
select
'test message: '
+ case
when a=0 then 'message 1'
when a<>0 then 'message 2'
else 'XXXXX'
end as SQLCOLUMN
-- string is completely dependant on value of a
select
case
when a=0 then 'test message: message 1'
when a<>0 then 'test message: message 2'
else 'XXXXX'
end as SQLCOLUMN
February 28, 2014 at 8:32 am
HanShi (2/28/2014)
If you want the option to have 'XXXXX' as a complete textstring instead of this added after the intial string, then you need to put the initial string inside the case statement.
-- result will always start with string "test message: "select
'test message: '
+ case
when a=0 then 'message 1'
when a<>0 then 'message 2'
else 'XXXXX'
end as SQLCOLUMN
-- string is completely dependant on value of a
select
case
when a=0 then 'test message: message 1'
when a<>0 then 'test message: message 2'
else 'XXXXX'
end as SQLCOLUMN
This logic your ELSE will never get hit because A will be either 0 or not 0. I think you want when a=0... when a=1.... else 'xxxxx'
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
February 28, 2014 at 8:41 am
Kurt W. Zimmerman (2/28/2014)
This logic your ELSE will never get hit because A will be either 0 or not 0. I think you want when a=0... when a=1.... else 'xxxxx'Kurt
If a has no value (i.e. IS NULL) it will hit the ELSE clause.
declare @a int
select
'test message: '
+ case
when @a=0 then 'a equals 0'
when @a<>0 then 'a is a non-zero number'
else 'a is a NULL value'
end as SQLCOLUMN
Running the above code will result in:
test message: a is a NULL value
February 28, 2014 at 8:44 am
Kurt W. Zimmerman (2/28/2014)
HanShi (2/28/2014)
If you want the option to have 'XXXXX' as a complete textstring instead of this added after the intial string, then you need to put the initial string inside the case statement....
...
This logic your ELSE will never get hit because A will be either 0 or not 0. I think you want when a=0... when a=1.... else 'xxxxx'
Kurt
If column [a] Is Null, then both conditions "a = 0" and "a <> 0" will evaluate as False, and 'XXXXX' will be returned. For example:
declare @a int = null;
select
case
when @a = 0 then 'message 1'
when @a <> 0 then 'message 2'
else 'XXXXX'
end as SQLCOLUMN;
SQLCOLUMN
---------
XXXXX
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply