7 Replies Latest reply on Jul 25, 2018 6:26 AM by Ken Flerlage

    Conversion Failed When Converting

    Diane Royer

      I have the attached query that I am trying to run in Tableau.  The query ran fine until I added the line that reads "COUNT(EX.ID_NUM) OVER (PARTITION BY EX.ID_NUM , EX.ADV_TREE_YR_CDE ) AS NUM_DEGREES,".  After I added that line, Tableau is all of a sudden giving me a message that states "Conversion failed when converting the varchar value '4A' to data type int".  I don't understand this message.  None of the fields used in the above line contain the value '4A'.  All I want to do, is count the number of degrees a student has worked on in a given year.  Do you know why it would give me this message and how I could correct it?

       

      Any assistance you can provide would be greatly appreciated.

        • 1. Re: Conversion Failed When Converting
          Ken Flerlage

          It is unlikely that Tableau is actually the thing that is giving this error. That looks more like an error that you'd get from the database platform. Am I correct in my assumption that this is a SQL Server database? If so, could you run this query in SQL Server Management Studio and see if you get the same error?

          • 2. Re: Conversion Failed When Converting
            Diane Royer

            You are correct.  I put the query in SQL Server Management Studio and received the same error.  I still don't understand why I'm getting the error though.  There is no "4A" in my data.  Do you know why SQL would give that error?

            • 3. Re: Conversion Failed When Converting
              Ken Flerlage

              Can you try removing "COUNT(EX.ID_NUM) OVER (PARTITION BY EX.ID_NUM , EX.ADV_TREE_YR_CDE ) AS NUM_DEGREES," and run it in Management Studio? Does it run okay or do you still get the error?

              • 4. Re: Conversion Failed When Converting
                Diane Royer

                It runs fine if I take that line out (no error messages).  Is there any way to count the number of times a student appears during a given school year?

                • 5. Re: Conversion Failed When Converting
                  Diane Royer

                  I just tried it again and I received an error message this time.  I tried putting the two numbers in my "between" statement in quotation marks and then it ran fine.  I really don't understand what happened, but it seems to be working now.  Thank you for your assistance.

                  • 6. Re: Conversion Failed When Converting
                    Ken Flerlage

                    Is it possible that a new TRM_CDE was entered into the system as "A4"? If that's the case, then it's possible that prior to this new entry, all the values were numeric. In that case, SQL Server could handle the numeric values 10 and 98 because all values in the table were numeric (even though the field itself is a string). Essentially, SQL Server would first convert all the values in the field to numbers then perform the comparison. But once that actual string value, A4, was entered, SQL could no longer convert all the values to a number in order to do the comparison. Thus the failure. So, good rule of thumb is to always use single quotes if the field is a string, even if the values all appear to be numeric.

                     

                    Hope that helps. Could you mark one of the answers as correct (even if it's your own), so that we can close out this thread and others can quickly find the solution in the future? Thanks!