Skip to content
Advertisement

SQL: use CASE with AS

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

Advertisement

Answer

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
Advertisement