I'm working on a project where I'm trying to understand some workflow volumes based on some data I have. There is data held within a field that I need to strip out, group together and then count, the field in question is called "Annotation" and field text will always start with "Annotation"..I've compiled the below, but I can't seem to pull any information back, I'm probably missing something very simple, any ideas
WITH RESULTS AS (
WHEN ANNOTATION LIKE '%No Action%' THEN 'No Action Taken'
WHEN ANNOTATION LIKE '%Awaiting Customer%' THEN 'Awaiting Response'
WHEN ANNOTATION LIKE '%Response Posted%' THEN 'Reponse Posted'
WHEN ANNOTATION LIKE '%Under Investigation%' THEN 'Post Under Investigation'
END AS RESULTTYPE
WHERE ANNOTATION LIKE '%ANNOTATION%')
SELECT RESULTTYPE, COUNT(*)
WHERE RESULTType IN ('No Action Taken', 'Awaiting Response','Reponse Posted','Post Under Investigation')
GROUP BY RESULTTYPE;
Another approach could be involving, for example, UPPER function:
where upper(annotation) like '%ANNOTATION%'
However, for large tables, it might cause problems (i.e. slower query execution) if there was an index on the ANNOTATION column, which would become useless because of the UPPER function. You could create a function based index, though (but now we're stepping into a performance tuning area).