if statement - Case when statement SQL-CodePudding

I'm trying to include a statement where if MISTI returns DOWN then I want it to exclude MISTI, MISTI-A, MISTI-B from the output list. Is this possible? I am new to SQL so any assistance would be highly appreciated!

Example: AS480 returned as DOWN therefor I dont want it returning AS480, AS480-A, AS480-B.

Table Output:

Select CASE WHEN entity like 'AS480%' then 'A'
WHEN entity like 'ET621%' then 'A'
WHEN entity like 'SZ101%' then 'A'
WHEN entity like 'HD801%' then 'A'
WHEN entity like 'ET328%' then 'A' 
  ELSE 'NONE'
       end as GRID, 
entity as MISTI , cur_state as STATE, ROUND(((SYSDATE-cur_state_dttm)*24),2) AS HRS
      FROM trk_id_def


WHERE cur_state IN ('NM', 'DOWN')
and (entity like 'AS480%'
or entity like 'ET212%'
or entity like 'ET213%'
or entity like 'ET216%'
or entity like 'ET218%'
or entity like 'ET221%'
or entity like 'ET225%'
or entity like 'ET208%'
)
AND ROUND(((SYSDATE-cur_state_dttm)*24),2) >= 0
order by GRID ASC

CodePudding user response：

Assuming:

The parent entity is always 5 characters
You only want to exclude the child entities A and B (and no others)

You can use something along these lines:

SELECT CASE
         WHEN entity LIKE 'AS480%' THEN 'A'
         WHEN entity LIKE 'ET621%' THEN 'A'
         WHEN entity LIKE 'SZ101%' THEN 'A'
         WHEN entity LIKE 'HD801%' THEN 'A'
         WHEN entity LIKE 'ET328%' THEN 'A'
         ELSE 'NONE'
       END AS GRID,
       entity AS MISTI,
       cur_state AS STATE,
       ROUND(((SYSDATE - cur_state_dttm)*24),2) AS HRS
FROM trk_id_def x
WHERE cur_state IN ('NM','DOWN')
AND   (entity LIKE 'AS480%' 
    OR entity LIKE 'ET212%' 
    OR entity LIKE 'ET213%' 
    OR entity LIKE 'ET216%' 
    OR entity LIKE 'ET218%' 
    OR entity LIKE 'ET221%' 
    OR entity LIKE 'ET225%' 
    OR entity LIKE 'ET208%')
AND   ROUND(((SYSDATE - cur_state_dttm)*24),2) >= 0
AND   NOT EXISTS (
                  SELECT 1 FROM trk_id_def y 
                  WHERE LENGTH(y.entity) = 5 --presumably (AS480, ETXXX etc)
                    AND y.cur_state = 'DOWN' --the parent entity is down
                    AND SUBSTR(x.entity,1,5) = y.entity --other entities related to the parent
                    AND (SUBSTR(x.entity,6,2) IN ('-A','-B') --just these suffixes
                          OR x.entity = y.entity) --or the parent itself
                          );

If you have the possibility of other suffixes of child entity names (-C, -D etc), and you want to exclude those as well, you can remove the AND (RIGHT... part.