August 3, 2011 at 3:30 am
Hello everyone,
I have a requirement, where i need to set some values in case statements
like
This statement
CASE ISNULL([Column 0],'')
WHEN '' THEN 'NOT A NULL FIELD'
ELSE [Column 0] + ' CITY'
END
AS testLOCATION
is working fine in a select statement.
But my requirement is this:
DECLARE @test-2 VARCHAR(10)
(CASE ISNULL([Column 0],'')
WHEN
BEGIN
SET @test-2 =’TESTVALUE’
END
''
THEN
BEGIN
'At Location' + ISNULL([Column 0],'')
END
ELSE CAST([Column 4] AS VARCHAR) +' KMS ' + RTRIM(ISNULL([Column 0],'')) + ' OF ' +' CITY'
How can i fix this
August 3, 2011 at 3:44 am
I'm sorry, but I don't understand what you're trying to do. Why do you think you need to use a variable, and why do you set a value for that variable but not use it subsequently?
John
August 3, 2011 at 4:19 am
you want to evaluate two conditions in the same case statement like this?
select case when 1 = 1 and 2 = 2 then 'both' else 'not both' end
August 3, 2011 at 4:28 am
Thanks for the prompt reply.
However this is the equivalent C# code
If ([column1]is NULL)
Set [column1]= string.Empty;
If([column1]is NULL)
Set [column1] = 0;
If([column3]is NULL)
Set [column3]= string.Empty;
Switch(([column1])
{
([column1]equals string.Empty:
return ‘At ’ + ([column3];
default://for all other cases
return Convert.ToString(Round(([column2],4))
+ ‘ kms ’
+ Rtrim(([column0])
+ ‘ of ’
+ ([column3];
}
August 3, 2011 at 4:44 am
Hi, You can try something like:
SELECT
ISNULL([column1], '') AS A
, ISNULL([column1], 0) AS B
, ISNULL([column3], '') AS C
, CASE WHEN ISNULL([Column 1],'') = '' THEN 'At ' + ISNULL([Column 3],'')
ELSE CAST([Column 4] AS VARCHAR) +' KMS ' + RTRIM(ISNULL([Column 0],'')) + ' OF ' +[column3]
END
FROM table_name
Regards,
Iulian
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply