Options
Where Like any combination of 4 values
slc1axj
Vertica Customer ✭
Part of my case statement is as follows:
when o.'service category' like '%DISP%' or o.'service category' like '%O/P%' or o.'service category' like '%RESP%' or o.'service category' like '%HME%' then 'Y'
However, if service category contains anything other than the 4 codes above, I don't want it labeled a Y - so basically the service category can only contain one of the following codes (DISP, O/P, RESP or HME) - the service category can contain multiple values
0
Best Answer
-
Options
Jim_Knicely
- Select Field - Administrator
There are a million ways to do this ... Here is one example:
verticademos=> SELECT "service category", CASE WHEN "service category" = '' THEN 'N' ELSE DECODE(REGEXP_REPLACE("service category", '\b(?:DISP|O/P|RESP|HME|,)\b', '', 1, 0), '', 'Y', 'N') END FROM o; service category | case ------------------+------ A,B,C | N DISP | Y DISP,O/P | Y RESP,O/P,A,Z | N O/P,HME,Q | N | N (6 rows)1
Answers
What does
service_categorynormally contain? YourLIKEpredicate suggests that you have something before one of the codes you mention and something after one of the codes you mention. What doesservice_categorycontain when your expressions returns 'N'? Depending on the answer, you could or could not replace the expression with anIN()predicate, or aREGEXP_LIKE()function. Can you share some examples?