MySQL - SELECT CASE WHEN THEN

Question

I must do a SELECT and format an output column to display the result below and I cannot use XML PATH because I am using MySQL, also cannot use WHERE because it is doing another condition.

I have this table:

I want this result:

I started to think in something like this:

, CASE
   WHEN csc.NAME LIKE ('Microsoft Office%')
    AND csc.NAME NOT IN ('Microsoft Office Shortcut Bar 2000','Office 2011 for Mac') THEN ???
   ELSE '-'
  END AS `sw_microsoft_office`

But I don't know how to display the conditional result after THEN.

This is the original Query:

SELECT DATE_FORMAT(om.BIOS_RELEASE_DATE, '%Y/%m/%d') AS `Ship_Date`
 , CASE
    WHEN ksc.CLIENT_CONNECTED = '0' THEN 'Not connected'
    WHEN ksc.CLIENT_CONNECTED = '1' THEN 'Conected'
    ELSE 'Not identified'
   END AS St
 , DATE_FORMAT(om.LAST_INVENTORY, '%Y/%m/%d') AS `Last_Inventory`
 , CASE
    WHEN (DATEDIFF(CURDATE() , DATE(om.LAST_INVENTORY)) >= 30) THEN 'Não'
    ELSE 'Sim'
   END AS `Inv_Less_Than_30`
 , om.NAME `Hostname`
 , om.IP `IP`
 , om.OS_NAME `OS`
 , CASE
    WHEN om.USER IS NULL THEN '-'
    WHEN om.USER = '' THEN '-'
    ELSE om.USER
   END AS `Usr`
 , CASE
    WHEN om.USER_FULLNAME IS NULL THEN '-'
    WHEN om.USER_FULLNAME = '' THEN '-'
    ELSE om.USER_FULLNAME
   END AS `User_Fullname`
 , CASE
    WHEN oaDepartment.Name IS NULL THEN '-'
    WHEN oaDepartment.Name = '' THEN '-'
    ELSE oaDepartment.Name
   END AS `Department`
 , CASE
    WHEN oad5.FIELD_10104 IS NULL THEN '-'
    WHEN oad5.FIELD_10104 = '' THEN '-'
    WHEN oad5.FIELD_10104 = 'Não tem' THEN '-'
    WHEN oad5.FIELD_10104 LIKE ('001%') THEN 'Company1'
    WHEN oad5.FIELD_10104 LIKE ('002%') THEN 'Company2'
    WHEN oad5.FIELD_10104 LIKE ('003%') THEN 'Company3'
    WHEN oad5.FIELD_10104 LIKE ('004%') THEN 'Company4'
    WHEN oad5.FIELD_10104 LIKE ('005%') THEN 'Company5'
    WHEN oad5.FIELD_10104 LIKE ('006%') THEN 'Company6'
    WHEN oad5.FIELD_10104 LIKE ('014%') THEN 'Company7'
    ELSE oad5.FIELD_10104
   END AS `Company`
 , CASE
    WHEN oaLocation.NAME IS NULL THEN '-'
    WHEN oaLocation.NAME = 'Unassigned' THEN '-'
    ELSE oaLocation.NAME
   END AS `Location`
 , CASE
    WHEN oaMachine.NAME IS NULL THEN '-'
    WHEN oaMachine.NAME = 'Unassigned' THEN '-'
    ELSE oaMachine.NAME
   END AS `Assets - St`
 , CASE
    WHEN oad5.FIELD_10231 IS NULL THEN '-'
    WHEN oad5.FIELD_10231 = '' THEN '-'
    ELSE oad5.FIELD_10231
   END AS `Situation`
 , CASE
    WHEN csc.NAME LIKE ('Microsoft Office%')
     AND csc.NAME NOT IN ('Microsoft Office Shortcut Bar 2000','Office 2011 for Mac') THEN ???
    ELSE '-'
   END AS sw_microsoft_office
FROM ORG1.MACHINE om
 LEFT JOIN KBSYS.SMMP_CONNECTION ksc ON om.KUID = ksc.KUID
 LEFT JOIN ORG1.ASSET oa ON om.ID = oa.MAPPED_ID
  AND oa.ASSET_TYPE_ID = 5
 LEFT JOIN ORG1.ASSET oaLocation ON oaLocation.ID = oa.LOCATION_ID
 LEFT JOIN ORG1.ASSET_DATA_5 oad5 ON oad5.ID = oa.ASSET_DATA_ID
 LEFT JOIN ORG1.ASSET_ASSOCIATION oaa ON oa.ID = oaa.ASSET_ID
  AND oaa.ASSET_FIELD_ID = 10109
 LEFT JOIN ORG1.ASSET_ASSOCIATION J10112 ON J10112.ASSET_ID = oa.ID AND J10112.ASSET_FIELD_ID = 10112
 LEFT JOIN ORG1.ASSET oaDepartment ON oaDepartment.ID = J10112.ASSOCIATED_ASSET_ID
 LEFT JOIN ORG1.ASSET oaMachine ON oaMachine.ID = oaa.ASSOCIATED_ASSET_ID
 INNER JOIN ORG1.SAM_MACHINE_JT osmj ON om.ID = osmj.MACHINE_ID
 INNER JOIN CATALOG.SAM_CATALOG csc ON osmj.SAM_CATALOG_ID = csc.ID
 INNER JOIN ORG1.SAM_COMPLIANCE_DETAIL oscd ON csc.ID = oscd.SAM_CATALOG_ID
WHERE om.NAME NOT LIKE ('GZT%')
 AND om.NAME NOT LIKE ('SERVER%')
 AND om.CS_MANUFACTURER NOT LIKE ('Apple%')
 AND om.CS_MANUFACTURER NOT LIKE ('Parallels%')
 AND om.CS_MANUFACTURER NOT LIKE ('VM%')
 AND om.OS_NAME LIKE ('Microsoft Windows XP%')
GROUP BY om.NAME

Why can't you use `WHERE` just because it is doing another condition? That is what `AND` and `OR` are for. — duncan, May 01 '17 at 13:03
Because in this example I am filtering machines with Windows XP in the WHERE clause, but I need to show which machine has and which does not has Microsoft Office Softwares. — Felippe Tabachi da Cruz, May 01 '17 at 13:07
there's one ways I can see doing this: write 7 case statements one for all three, one for any combination of the 2 (A,B), (B,C), (A,C) and one for each individually. The then becomes clear. You could use a 3 case statments checking for eahc of the values and then use CONCAT_WS to combine then back together... — xQbert, May 01 '17 at 13:33

score 0 · Accepted Answer · answered May 01 '17 at 13:04

0

I guess you want

, CASE WHEN csc.NAME LIKE ('Microsoft Office%') AND csc.NAME NOT IN ('Microsoft Office Shortcut Bar 2000','Office 2011 for Mac') THEN csc.NAME ELSE '-' END AS sw_microsoft_office

answered May 01 '17 at 13:04

Fran Cerezo

940
3
8
19

Could be, but it does not show me when computer has two softwares, in this case I need this separated by comma. – Felippe Tabachi da Cruz May 01 '17 at 13:12
It is showing the field, and it is comma separated. I do not understand you. – Fran Cerezo May 01 '17 at 13:16
Forgive me, I expressed myself badly. I wanted to say that in my query the result did not come separated by a comma. At this point I'm inserting the original query above. – Felippe Tabachi da Cruz May 01 '17 at 13:18

score 0 · Answer 2 · answered May 01 '17 at 22:39

I would like to thank everyone for helping me with this question! Your contribution made me arrive at the result I needed! Thank you very much!

, REPLACE(REPLACE(GROUP_CONCAT(
   CASE
    WHEN csc.NAME LIKE ('Microsoft Office%')
     AND csc.NAME NOT IN ('Microsoft Office Shortcut Bar 2000','Office 2011 for Mac') THEN csc.NAME
    ELSE '-'
   END
   ORDER BY CASE
             WHEN csc.NAME LIKE ('%2000%') THEN 1
             WHEN csc.NAME LIKE ('%2003%') THEN 2
             WHEN csc.NAME LIKE ('%2007%') THEN 3
             WHEN csc.NAME LIKE ('%2010%') THEN 4
             WHEN csc.NAME LIKE ('%2013%') THEN 5
             WHEN csc.NAME LIKE ('%2016%') THEN 6
             ELSE '7'
            END
    , CASE
       WHEN csc.NAME LIKE ('%Small Business%') THEN 1
       WHEN csc.NAME LIKE ('%Home and Business%') THEN 2
       WHEN csc.NAME LIKE ('%Standard%') THEN 3
       WHEN csc.NAME LIKE ('%Professional%') THEN 4
       ELSE '5'
      END
  ),',-',''),'-,','') AS sw_microsoft_office

MySQL - SELECT CASE WHEN THEN

2 Answers2