SQL : use CASE with AS-CodePudding

I'm working on a long SQL request and I want to use CASE inside but I don't know how to write it. My request looks like :

SELECT (SELECT id 
          FROM first_table ft 
               INNER JOIN second_table st ON st.id = ft.id 
               INNER JOIN third_table tt ON tt.id = ft.id 
         WHERE tt.second = 0 LIMIT 1) as key

I want to add something like : CASE key WHEN 'One' THEN 'Two' END

I tried to add this at the end but it didn't work :

 SELECT (SELECT id 
           FROM first_table ft 
                INNER JOIN second_table st ON st.id = ft.id 
                INNER JOIN third_table tt ON tt.id = ft.id 
          WHERE tt.second = 0 LIMIT 1) as key 
           CASE WHEN key = 'One' 
           THEN 'Two' END

My goal is to edit key along its value

CodePudding user response：

You can do a subquery:

SELECT
   key,
   CASE WHEN key = 'One' THEN 'Two' END test_column
FROM
   (
      SELECT 
         id key
      FROM
         first_table ft
         INNER JOIN second_table st ON st.id = ft.id
         INNER JOIN third_table tt ON tt.id = ft.id
      WHERE 
         tt.second = 0
      LIMIT 1
   ) data