The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Where Like any combination of 4 values

slc1axjslc1axj 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

Best Answer

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    Answer ✓

    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)


  • marcothesanemarcothesane - Select Field - Administrator

    What does service_category normally contain? Your LIKE predicate suggests that you have something before one of the codes you mention and something after one of the codes you mention. What does service_category contain when your expressions returns 'N'? Depending on the answer, you could or could not replace the expression with an IN() predicate, or a REGEXP_LIKE() function. Can you share some examples?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file