How to select the given case statement as column

Question

I want to query the table after giving case statement/

%sql SELECT user_id, user_name,\
       email,\
       CASE WHEN user_name = 'ruo' THEN 'No'\
            WHEN user_name = 'ruoman' THEN 'Yes' \
            else 'Other' END AS New_Data FROM Pes

%sql SELECT user_name from Pes where New_Data = 'Yes' --> this gives me error -> No such column

Answer 1

Either use the CASE expression in the WHERE clause:

SELECT user_name
FROM Pes
WHERE CASE user_name 
        WHEN 'ruo' THEN 'No'
        WHEN 'ruoman' THEN 'Yes' 
        ELSE 'Other' 
      END = 'Yes';

or, first select the CASE expression and use SQLite's feature to allow derived columns in the WHERE clause:

SELECT user_name,
       CASE user_name 
         WHEN 'ruo' THEN 'No'
         WHEN 'ruoman' THEN 'Yes' 
         ELSE 'Other' 
       END AS New_Data 
FROM Pes
WHERE New_Data = 'Yes';