4 Replies Latest reply on Jun 6, 2017 9:03 AM by GreggSmith

    What am I doing wrong with these CASE statements in Xtraction?

    ericpiety Rookie

      I have used CASE statements several times in the data model and they are all working. This new one give me the error below. Seems to corrolate with the GUID. As the "faultString = "Incorrect syntax near 'E'." changes if swap the first GUID to another.

       

      ####### CASE STAtements ######

      CASE

      WHEN CHANGE.USR_SERVICE = E1E0996E-88BA-473E-BF91-652753972EA5 THEN 'INCYT'

      WHEN CHANGE.USR_SERVICE = BF6F2226-0E8E-4397-86C6-0E5EEF4E0C4E THEN 'JIRA'

      WHEN CHANGE.USR_SERVICE = B94CC1F0-3751-4710-B855-6A842E9511D6 THEN 'OTHER'

      WHEN CHANGE.USR_SERVICE = C75640FC-A127-4607-BAE1-6C2491944637 THEN 'NETWORK SRVCS'

      WHEN CHANGE.USR_SERVICE = 1BDE0F5A-8883-49E6-B7BD-B42596778A93 THEN 'ECLAS'

      WHEN CHANGE.USR_SERVICE = 232511A1-B842-443B-B2B4-A5FF2855D3CA THEN 'EMAIL'

      WHEN CHANGE.USR_SERVICE = 6CB6A4CD-FAE9-4405-83A7-B2CCF920D968 THEN 'DESKTOP SRVCS'

      WHEN CHANGE.USR_SERVICE = 8BBAA33A-B25F-4AC2-8EAF-FC9F0D993AF7 THEN 'CRM'

      WHEN CHANGE.USR_SERVICE = 0284B871-CAB7-4B8B-B6BE-DD9FAC0B7B51 THEN 'NAVISION'

      WHEN CHANGE.USR_SERVICE = 35D7E56F-0B89-4F6D-8133-1F79FAEB93CF THEN 'AXWAY'

      WHEN CHANGE.USR_SERVICE = BA6D52DE-D98A-4992-A5FE-B24325FB3C36 THEN 'APPIAN'

      WHEN CHANGE.USR_SERVICE = 5705A894-F336-41A7-A24D-3031AE146CF3 THEN 'TELECOM SRVCS'

      WHEN CHANGE.USR_SERVICE = 80D64758-228C-492F-B315-10B2004815D1 THEN 'AESTIVA'

      WHEN CHANGE.USR_SERVICE = ABD849DC-E39F-434D-8840-9EE79B4DB688 THEN 'SHAREPOINT'

      WHEN CHANGE.USR_SERVICE = 3471BAA2-1583-49E4-8B4B-64E6C115036E THEN 'FILE PRINT SRVCS'

      WHEN CHANGE.USR_SERVICE = 89D7D965-7E38-4F8F-9577-84DF239CD75B THEN 'INTERNET'

      WHEN CHANGE.USR_SERVICE = 74CA8466-D4C7-427D-95FB-5ED6C2761B16 THEN 'BUSINESS OBJECTS'

      WHEN CHANGE.USR_SERVICE = B79A7CC9-57F3-4D33-9884-36BFE0A62293 THEN 'SERVER'

      WHEN CHANGE.USR_SERVICE = 4F2CB101-0F64-4E36-97BE-58D98E383194 THEN 'REMOTE ACCESS VPN'

      WHEN CHANGE.USR_SERVICE = A875C6C7-24A2-446D-BCBA-30392BB51D56 THEN 'REOCONNEX SL'

      WHEN CHANGE.USR_SERVICE = F2478B86-0A59-4CF3-B4BF-29AA2BAA9E18 THEN 'LANDESK'

      WHEN CHANGE.USR_SERVICE = 96DD7E2B-D17D-49B6-902F-6ACEBACE8162 THEN 'HELP DESK SITE'

      WHEN CHANGE.USR_SERVICE = 302F2E38-395B-4364-AEFF-FD7496F0DBD8 THEN 'PYRAMID'

      WHEN CHANGE.USR_SERVICE = 527A59BF-7692-4EC2-96AB-03F73B323AF1 THEN 'FILELOG'

      WHEN CHANGE.USR_SERVICE = 0DA6CCD8-AC94-47C7-B469-B01AC0C8F1AC THEN 'REPORT MNGR'

      WHEN CHANGE.USR_SERVICE = F368DD33-9C4A-4E6F-AFBC-10368F9FAAF4 THEN 'REOCONNEX LEGACY'

      WHEN CHANGE.USR_SERVICE = 12322B8A-D460-4996-81CA-8E11607E5363 THEN 'DOCVELOCITY'

      WHEN CHANGE.USR_SERVICE = 059D0D00-D074-4F8E-A623-A0D7BF287BCB THEN 'GRC REPORT MNGR'

      WHEN CHANGE.USR_SERVICE = 2161CDE2-FDBC-4878-A5BF-A764B0A83C7A THEN 'AGENTCONNEX'

      ELSE 'Unknown (' + CAST(CHANGE.USR_SERVICE AS NVARCHAR(10)) + ')'

      END

       

      ######## ERROR #######

      (mx.messaging.messages::ErrorMessage)#0

        body = (null)

        clientId = "12124a3c-9aa4-4cff-9121-1c293c7ff85d"

        correlationId = "1DCA04C6-AFAA-0E76-8DCF-6925F65CDDAA"

        destination = "fluorine"

        extendedData = (Object)#1

        faultCode = "Server.Processing"

        faultDetail = (null)

        faultString = "Incorrect syntax near 'E'."

        headers = (Object)#2

          DSId = "e4ce972f366047c6898081213d5b29b5"

        messageId = "16d644be-41eb-42d8-aaaa-9cf6debefa0f"

        rootCause = (null)

        timestamp = 1512393671

        timeToLive = 0

        • 1. Re: What am I doing wrong with these CASE statements in Xtraction?
          phoffmann SupportEmployee

          Hmm - the first thing that comes to mind is that you're not encapsulating your strings with '-s as part of the CASE statement.

           

          Try this perhaps:

          -- ####### CASE Statements ######
          
          CASE
          WHEN CHANGE.USR_SERVICE = 'E1E0996E-88BA-473E-BF91-652753972EA5' THEN 'INCYT'
          WHEN CHANGE.USR_SERVICE = 'BF6F2226-0E8E-4397-86C6-0E5EEF4E0C4E' THEN 'JIRA'
          WHEN CHANGE.USR_SERVICE = 'B94CC1F0-3751-4710-B855-6A842E9511D6' THEN 'OTHER'
          WHEN CHANGE.USR_SERVICE = 'C75640FC-A127-4607-BAE1-6C2491944637' THEN 'NETWORK SRVCS'
          WHEN CHANGE.USR_SERVICE = '1BDE0F5A-8883-49E6-B7BD-B42596778A93' THEN 'ECLAS'
          WHEN CHANGE.USR_SERVICE = '232511A1-B842-443B-B2B4-A5FF2855D3CA' THEN 'EMAIL'
          WHEN CHANGE.USR_SERVICE = '6CB6A4CD-FAE9-4405-83A7-B2CCF920D968' THEN 'DESKTOP SRVCS'
          WHEN CHANGE.USR_SERVICE = '8BBAA33A-B25F-4AC2-8EAF-FC9F0D993AF7' THEN 'CRM'
          WHEN CHANGE.USR_SERVICE = '0284B871-CAB7-4B8B-B6BE-DD9FAC0B7B51' THEN 'NAVISION'
          WHEN CHANGE.USR_SERVICE = '35D7E56F-0B89-4F6D-8133-1F79FAEB93CF' THEN 'AXWAY'
          WHEN CHANGE.USR_SERVICE = 'BA6D52DE-D98A-4992-A5FE-B24325FB3C36' THEN 'APPIAN'
          WHEN CHANGE.USR_SERVICE = '5705A894-F336-41A7-A24D-3031AE146CF3' THEN 'TELECOM SRVCS'
          WHEN CHANGE.USR_SERVICE = '80D64758-228C-492F-B315-10B2004815D1' THEN 'AESTIVA'
          WHEN CHANGE.USR_SERVICE = 'ABD849DC-E39F-434D-8840-9EE79B4DB688' THEN 'SHAREPOINT'
          WHEN CHANGE.USR_SERVICE = '3471BAA2-1583-49E4-8B4B-64E6C115036E' THEN 'FILE PRINT SRVCS'
          WHEN CHANGE.USR_SERVICE = '89D7D965-7E38-4F8F-9577-84DF239CD75B' THEN 'INTERNET'
          WHEN CHANGE.USR_SERVICE = '74CA8466-D4C7-427D-95FB-5ED6C2761B16' THEN 'BUSINESS OBJECTS'
          WHEN CHANGE.USR_SERVICE = 'B79A7CC9-57F3-4D33-9884-36BFE0A62293' THEN 'SERVER'
          WHEN CHANGE.USR_SERVICE = '4F2CB101-0F64-4E36-97BE-58D98E383194' THEN 'REMOTE ACCESS VPN'
          WHEN CHANGE.USR_SERVICE = 'A875C6C7-24A2-446D-BCBA-30392BB51D56' THEN 'REOCONNEX SL'
          WHEN CHANGE.USR_SERVICE = 'F2478B86-0A59-4CF3-B4BF-29AA2BAA9E18' THEN 'LANDESK'
          WHEN CHANGE.USR_SERVICE = '96DD7E2B-D17D-49B6-902F-6ACEBACE8162' THEN 'HELP DESK SITE'
          WHEN CHANGE.USR_SERVICE = '302F2E38-395B-4364-AEFF-FD7496F0DBD8' THEN 'PYRAMID'
          WHEN CHANGE.USR_SERVICE = '527A59BF-7692-4EC2-96AB-03F73B323AF1' THEN 'FILELOG'
          WHEN CHANGE.USR_SERVICE = '0DA6CCD8-AC94-47C7-B469-B01AC0C8F1AC' THEN 'REPORT MNGR'
          WHEN CHANGE.USR_SERVICE = 'F368DD33-9C4A-4E6F-AFBC-10368F9FAAF4' THEN 'REOCONNEX LEGACY'
          WHEN CHANGE.USR_SERVICE = '12322B8A-D460-4996-81CA-8E11607E5363' THEN 'DOCVELOCITY'
          WHEN CHANGE.USR_SERVICE = '059D0D00-D074-4F8E-A623-A0D7BF287BCB' THEN 'GRC REPORT MNGR'
          WHEN CHANGE.USR_SERVICE = '2161CDE2-FDBC-4878-A5BF-A764B0A83C7A' THEN 'AGENTCONNEX'
          ELSE 'Unknown (' + CAST(CHANGE.USR_SERVICE AS NVARCHAR(10)) + ')'
          END
          

           

          ... not using '-s can be a big problem, especially when you have "-" characters in there ... SQL is likely attempting to math-hammer out what is essentially a single string ... but not having defined it as such, you cause SQL issues.

           

          If you put the single-quotes around it (to help clarify that "this is a single string"), you may have more joy?

          • 2. Re: What am I doing wrong with these CASE statements in Xtraction?
            GreggSmith SupportEmployee

            If you HAVE to use a CASE statement, then the above fix of adding single quotes around the GUIDs should be the solution.

             

            However, GUIDs usually mean there is another table somewhere with values for those GUIDs.  Is there not a Service table that lists those Service names associated with the GUIDs?  It would be easier to maintain the data model and definitely faster performance if you link the CHANGE.USR_SERVICE field to some table that lists those Services.

            • 3. Re: What am I doing wrong with these CASE statements in Xtraction?
              ericpiety Rookie

              Still getting an error.

               

              (mx.messaging.messages::ErrorMessage)#0

                body = (null)

                clientId = "479dfc38-f8f7-4770-ac3a-55ef4f835ead"

                correlationId = "FA234F4B-FB2B-5E4A-6B2A-79E92241A73D"

                destination = "fluorine"

                extendedData = (Object)#1

                faultCode = "Server.Processing"

                faultDetail = (null)

                faultString = "Arithmetic overflow error converting expression to data type nvarchar."

                headers = (Object)#2

                  DSId = "8eee091f451b425f8b6ac4100a9f3413"

                messageId = "768b51e6-d708-4c79-843a-60898b3db36a"

                rootCause = (null)

                timestamp = 1793616890

                timeToLive = 0

              • 4. Re: What am I doing wrong with these CASE statements in Xtraction?
                GreggSmith SupportEmployee

                It MIGHT be because the following line

                 

                ELSE 'Unknown (' + CAST(CHANGE.USR_SERVICE AS NVARCHAR(10)) + ')'

                 

                is trying to squeeze a 36-character field into 10 characters?  Try changing it to

                 

                ELSE 'Unknown (' + CAST(CHANGE.USR_SERVICE AS NVARCHAR(36)) + ')'