SQLite conditional output

Question

I need to output a X in a column in a report created using ONLY SQLite.

I need it to find a patern, and if that pattern exsists for that record output a X, if its not found output a blank space.

Heres what I have.

SELECT `device_type` AS "Device",
SUBSTR(`model`, 1, 30) AS "Model",
  `location` AS "Location",
  (CASE WHEN (`user_tag` LIKE "%decommissioned%" THEN "X" ELSE " " END) AS "Decom",
  count(`id`) AS "Count"
FROM `devices`
GROUP BY `device_type` ORDER BY `device` ASC;

Its reporting

near "THEN": syntax error

Like I said, I can ONLY use SQL for putting the report together. Its a little limiting but its all im allowed to use for this.

Thanks in advance for your help.

Answer 1

I think this is the right syntax:

SELECT `device_type` AS Device,
       SUBSTR(`model`, 1, 30) AS Model,
       `location` AS "Location",
        (CASE WHEN `user_tag` LIKE "%decommissioned%" THEN "X" ELSE " " END) AS "Decom",
        count(`id`) AS "Count"
FROM `devices`
GROUP BY device_type, SUBSTR(`model`, 1, 30), location,
         (CASE WHEN `user_tag` LIKE "%decommissioned%" THEN "X" ELSE " " END)
ORDER BY `device` ASC;

You have an extra opening paren. But in addition, the group by clause does not match the select in terms of the columns being used to define groups.