How to use select Case in the following SQL query

Question

I have two tables users and userdetail. I am trying to create a view in which if status of userdetail column is 1 it should show Active in view and blocked if status is 0:

CREATE VIEW `new` AS 
SELECT 
users.id AS id,
userdetail.healthissues AS healthissues,
users.fullName AS fullname,
userdetail.`status`,

 CASE status
 WHEN  userdetail.status ='1' THEN  userdetail.`status`= 'Blocked'
        WHEN  userdetail.status= '0' THEN  userdetail.`status` ='Active'
    END ,

users.phoneNumber AS number
FROM users
JOIN userdetail ON users.id = userdetail.reference

This code does not give the desired result. Can some please help me out with this?

What are you trying to do? (E.g. when userdetail.status ='1', what's expected to be returned? Are you trying to change userdetail.status' value like?) — jarlh, Apr 09 '17 at 19:28

score 1 · Accepted Answer · answered Apr 09 '17 at 19:27

I think this is the correct syntax for what you want:

CREATE VIEW `new` AS 
    SELECT u.id AS id, ud.healthissues AS healthissues, u.fullName, 
           ud.status as orig_status,
           (CASE WHEN ud.status = 1 THEN 'Blocked'
                 WHEN ud.status = 0 THEN 'Active'
            END) as status,
           u.phoneNumber AS number
    FROM users u JOIN
         userdetail ud
         ON u.id = ud.reference;

Notes:

I'm not sure if you want to select the status as well as the string, but you have it in your query.
The correct syntax for case does not use = then the then clauses (well, unless you want it to return a boolean).
Table aliases make the query easier to write and to read.
New is a bad name for a view. Although not reserved, it is a keyword.

`NEW` is reserved according to ANSI/ISO SQL. (From SQL-99 and later.) — jarlh, Apr 09 '17 at 19:33

score 0 · Answer 2 · answered Apr 09 '17 at 19:28

You can't assign value .. just select

CREATE VIEW `new` AS 
SELECT 
users.id AS id,
userdetail.healthissues AS healthissues,
users.fullName AS fullname,
userdetail.`status`,
   CASE status
   WHEN  userdetail.status ='1' THEN  'Blocked'
          WHEN  userdetail.status= '0' THEN 'Active'
      END  as my_eval_status,
users.phoneNumber AS number
FROM users
JOIN userdetail ON users.id = userdetail.reference

How to use select Case in the following SQL query

2 Answers2