change value from query when running

Question

SELECT 
    'https://xxxxxx/' + CONVERT(VARCHAR(10), [AnnouncementID]) AS 'URL', 
    [Status] AS 'Code', 
    [Subject] AS 'Text', 
    (CASE 
        WHEN LEN([Subject]) > 500 
           THEN SUBSTRING([Subject], 0, 500) + '...' 
           ELSE [Subject] END) 
    AS 'ShortText'
FROM 
    [Announcement] 
WHERE 
    [Public] = 1 
    AND [Enable] = 1 
    AND GETDATE() BETWEEN [ActiveFrom] AND [ActiveTo] 
ORDER BY 
    [Status] ASC, [ActiveFrom] ASC

This query returns something like:

Url                 Code        Text    ShortText
-------------------------------------------------
https://xxxxxx/2    Enable      Text    Text
https://xxxxxx/1    Critical    Text    Text

I would like to Automatic change the text in [Code].

If Critical -> RED
If Enabled -> YELLOW

How can I rewrite my query string above?

please post expected result and also tag the DBMS you are using — TheGameiswar, Nov 16 '16 at 14:04
What i expect is that the out will change Enable -> YELLOW and Critical -> RED. so that the Query place the values — , Nov 16 '16 at 14:05
you might want to add more info on why enable should be changed to yellow and critical to red — TheGameiswar, Nov 16 '16 at 14:07

score 1 · Answer 1 · answered Nov 16 '16 at 14:12

1

use a CASE:

CASE [Status]
    WHEN 'Enable' then 'YELLOW'
    WHEN 'Critical'  then 'RED'
END as 'Code'

or use a JOINED table with its values.

answered Nov 16 '16 at 14:12

McNets

10,352
3
32
61

score 0 · Answer 2 · 2016-11-16T15:30:26.167

If I understand correctly; you want to change the words Enable and Critical to YELLOW and RED respectively?

You can do this with a case statement, as you haven't tagged what DBMS you are using I can't really help with syntax but a rough idea would be;

SELECT 'https://xxxxxx/' + CONVERT(VARCHAR(10), [AnnouncementID]) AS 'URL', 
Case [Status]
  when 'Critical' then 'RED'
  when 'Enable' then 'YELLOW'
  end AS 'Code' , 
[Subject] AS 'Text', 
(CASE WHEN LEN([Subject]) > 500 THEN SUBSTRING([Subject], 0, 500) + '...' ELSE [Subject] END) AS 'ShortText' 

FROM [Announcement] 

WHERE [Public] = 1 
AND [Enable] = 1 
AND GETDATE() BETWEEN [ActiveFrom] AND [ActiveTo] 

ORDER BY [Status] ASC, [ActiveFrom] ASC

It's effectively saying if [Status] has the value 'Critical' display 'RED' or if has the value 'Enable' then display 'YELLOW'

score 0 · Answer 3 · edited May 23 '17 at 12:33

0

Check out this answer. It references this article. With some minor modifications, adding a few CASE statements based on the value of [Code] you could add a <td style="color: red;"> or a <td style="color: yellow;">.

I hope that helps!

edited May 23 '17 at 12:33

Community

1
1

answered Nov 16 '16 at 14:19

grom

71
5

change value from query when running

3 Answers3