I want to write an SQL query to return rows with one distinct field and use CASE to create new evaluation column. Any help is appreciated. Deets below:

table

pseudo query:

return (distinct id), time_created, NEW_COL where category is 123123 and where new_col //create new col with these values ( if status = 'good' then 'GOOD' if status = 'bad' then 'BAD' )
FROM table

result:

--- I keep getting a lint error right on my CASE keyword: "expecting " '%', '*', ' ', '-', '.', '/', 'AT', '[', '||',""

one of queries I tried: SELECT ID, time_created CASE WHEN status = 'good' THEN 'GOOD' WHEN status = 'bad' THEN 'BAD' END as STATUS_new FROM TBL WHERE CATEGORY = '871654671' ORDER BY time_created

CodePudding user response：

You just have a small syntax error (and bad column name in your sql fiddle). You just need a comma after the time created column.

SELECT
        ID, time as time_created,
        CASE 
          WHEN status = 'good' THEN 'GOOD' 
          WHEN status = 'bad' THEN 'BAD'
         END
         as STATUS_new
 FROM TBL
 WHERE CATEGORY = '871654671'
 ORDER BY time_created

CodePudding user response：

Here is the working query: http://www.sqlfiddle.com/#!18/7293b5/11

 SELECT
       ID, TIME, 'STATUS_new' =
        CASE STATUS
          WHEN 'good' THEN 'GOOD' 
          WHEN 'bad' THEN 'BAD'
         END
 FROM TBL
 WHERE CATEGORY = '871654671'
 ORDER BY TIME

1. you must put the new name of the column before the CASE
2. the column you are defining the CASE must be defined directly behind the case and all the WHEN conditions are directly related to it.
3. in your fiddle you used the wrong column name of your TIME column