1 Reply Latest reply on Apr 26, 2017 2:32 PM by GreggSmith

    Is it possible to have multiple "CASE WHEN" statements in a field?

    ericpiety Rookie

      I have 12 items i would like to write a case for. PROBLEM.USR_ROOTCAUSECATEGORY

      I am unsure of this is even possible. I can use CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 0 THEN 'HUMAN ERROR' END, then get back only the items that are Human Error, displaying the text and not the number. But this ignores the rest. How does one write the case statement for the following? If I add them all I get all sorts of errors.

       

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 0 THEN 'HUMAN ERROR' END

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 1 THEN 'THIRD PARTY' END

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 2 THEN 'HARDWARE FAILURE' END

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 3 THEN 'CONFIGURATION' END

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 4 THEN 'ENVIRONMENTAL' END

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 5 THEN 'HARDWARE CAP/LOAD' END

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 6 THEN 'VIRUS' END

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 7 THEN 'PROCEDURAL' END

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 8 THEN 'DATABASE' END

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 9 THEN 'SOFTWARE UPGRADE' END

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 10 THEN 'UNKNOWN' END

      CASE WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 11 THEN 'CHANGE CONTROL' END

       

      Thanks

      E

        • 1. Re: Is it possible to have multiple "CASE WHEN" statements in a field?
          GreggSmith SupportEmployee

          CASE

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 0 THEN 'HUMAN ERROR'

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 1 THEN 'THIRD PARTY'

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 2 THEN 'HARDWARE FAILURE'

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 3 THEN 'CONFIGURATION'

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 4 THEN 'ENVIRONMENTAL'

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 5 THEN 'HARDWARE CAP/LOAD'

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 6 THEN 'VIRUS'

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 7 THEN 'PROCEDURAL'

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 8 THEN 'DATABASE'

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 9 THEN 'SOFTWARE UPGRADE'

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 10 THEN 'UNKNOWN'

            WHEN PROBLEM.USR_ROOTCAUSECATEGORY = 11 THEN 'CHANGE CONTROL'

            ELSE 'Unknown (' + CAST(PROBLEM.USR_ROOTCAUSECATEGORY AS NVARCHAR(10)) + ')'

          END

           

          or, it can be written like this:

           

          CASE PROBLEM.USR_ROOTCAUSECATEGORY

            WHEN 0 THEN 'HUMAN ERROR'

            WHEN 1 THEN 'THIRD PARTY'

            WHEN 2 THEN 'HARDWARE FAILURE'

            WHEN 3 THEN 'CONFIGURATION'

            WHEN 4 THEN 'ENVIRONMENTAL'

            WHEN 5 THEN 'HARDWARE CAP/LOAD'

            WHEN 6 THEN 'VIRUS'

            WHEN 7 THEN 'PROCEDURAL'

            WHEN 8 THEN 'DATABASE'

            WHEN 9 THEN 'SOFTWARE UPGRADE'

            WHEN 10 THEN 'UNKNOWN'

            WHEN 11 THEN 'CHANGE CONTROL'

            ELSE 'Unknown (' + CAST(PROBLEM.USR_ROOTCAUSECATEGORY AS NVARCHAR(10)) + ')'

          END

           

          I also added a last statement handles any records that have a value other than the 12 you explicitly listed.  Anything else will return "Unknown (13)" or "Unknown (35)" or whatever value that is in that field outside of the defined 0-11.

           

          I hope that helps.

           

          Gregg