Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '*%text%*4' to data type int

Question

I have a database that holds information linking customer data to forms that are filled out by them, or on them by someone else. Our UI runs reports but can only run 1 form at a time. I am trying to get an Excel type report out of the DB using SQL Query to combine tables to give me all forms and members connected to said form. Our Member table holds all the customer data but only has one column (UID) that has the same data on other tables with different column names. I have used the query below to fix this mismatch:

SELECT *
FROM dbo.Answer a
INNER JOIN dbo.tasklist_data tld ON a.source_taskID = tld.TaskID
INNER JOIN dbo.Member m ON tld.createdby = m.uid
INNER JOIN dbo.Form f ON a.FormID = f.FormID
INNER JOIN dbo.Element_Answer ea ON a.ID = ea.AnswerID
INNER JOIN dbo.Form_Elements fe ON ea.ElementID = fe.ElementID 

The problem I am having is one table (Element_Answer) has a column called Element_answer that holds the data of what was picked when the form was filled out. The data in this column is mainly integers ranging from 1 to 9 which link to another table (Form_Element_groups) that has the same number data in a column called sort_order. The ea.Element_Answer column also has text data (*%text%*75193) that represents another table (Element_answer_text) that holds the actual text that was written on the form. The number next to the %text% represents the ID on the Element_Answer_text table.

I am trying to combine the two tables(ea and feg). When I try to INNER JOIN tables Element_Answer to Form_Element_Groups using the ea.Element_Answer and feg.Sort_order columns as the common, it gives me the above error.

My desired output would be to be able to combine these two tables, so that I can see all my data, possibly ignoring all the %test% entries or possibly having the actual text from the Element_answer_text table replace the %text% entries.

I apologize in advance if I gave too much information. The tables in question are the Element_Answer table, which holds 5 columns (ID, AnswerID, ElementID, Element_answer, and status). The Element_answer column is important because it holds the integer data that I need that links to the other table called Form_Element_Groups that has 7 columns (ID, elementID, option_span, sort_order, group_title, group_text, and category). The sort_order column is the column I am trying to match up with the element_answer column because the data that matches up on these two columns will give me the desired result of only what I want to see from the feg table's group_title column. The ea.Element_answer column also has text entries that link to the Element_Answer_Text table that has two columns (ID, and Element_Answer_text). The eat.element_answer_text column holds the actual text.

If I can get a query that ignores the text in the ea.Element_answer column and only gives me my integer data that matches up, that would be great. If I can get a query that can not only give me my integer data that matches, but the actual text from the Element_Answer_text table, that would be even greater.

I can't figure out how to match the text data in the ea.Element_answer column with the actual text located on eat.Element_answer_text table/column because it has this weird *%text%*4 data within the ea.Element_answer column referring to the ID on the eat table. (I just picked 4 as an example, there are many other *%text% entries in that ea.Element_answer column that start with *%text%*4 and end with *%text%*95042.)

Answer 1

The problem is that Element_Answer.Element_Answer column is referencing two distinct tables. The weird *%text%* prefix in some of the rows is apparently there just to tell whether the value references one table or the other. Nevertheless, the fact that two different tables are referenced from a single column is a violation of any normalisation rules.

So, I strongly believe that the best solution to this would include a redesign of the Element_Answer table, so as to split its Element_Answer column into two: one referencing Form_Element_Groups.sort_order and the other referencing Element_Answer_text.ID. Both columns would be numeric as they both are essentially just references to numeric IDs.

For the purposes of the scripts below, I am calling the column referencing feg.sort_order by the same name, sort_order, for consistency, and the other reference is called Element_Answer_text_ID. Feel free to replace the names with whatever you consider more appropriate.

So, first you create two new integer columns in Element_Answer:

ALTER TABLE dbo.Element_Answer
ADD sort_order int NULL
    CONSTRAINT FK_ElementAnswer_FEG
    FOREIGN KEY REFERENCES dbo.Form_Element_Groups (sort_order);

ALTER TABLE dbo.Element_Answer
ADD Element_Answer_text_ID int NULL
    CONSTRAINT FK_ElementAnswer_EAT
    FOREIGN KEY REFERENCES dbo.Element_Answer_text (ID);

Note: the above assumes that both feg.sort_order and eat.ID are either declared as PKs or have a UNIQUE constraint defined on them. Otherwise they cannot be referenced. You can still store references without a formally defined relationship between tables, but you cannot have guaranteed data integrity that way, so I recommend you make sure you can declare the new columns as foreign keys.

The next step would be to populate the new columns from the current ea.Element_Answer one. That can be done in one go with an UPDATE statement like this:

UPDATE
  dbo.ElementAnswer
SET
  sort_order = CASE LEFT(ElementAnswer, 8) WHEN '*%text%*' THEN NULL ELSE Element_Answer END,
  Element_Answer_text_ID = CASE LEFT(ElementAnswer, 8) WHEN '*%text%*' THEN SUBSTRING(Element_Answer, 9, 999999) END
;

Now that the references to different tables sit in different columns, you can drop the original:

ALTER TABLE dbo.Element_Answer
DROP COLUMN Element_Answer;

To retrieve data from Form_Element_Groups and Element_Answer_text, you can complete your query with outer joins to those tables in this manner:

SELECT
  ...
  feg.group_title,
  eat.Element_Answer_text,
  ...
FROM
  ...
  LEFT JOIN dbo.Form_Element_Groups feg ON ea.sort_order = feg.sort_order
  LEFT JOIN dbo.Element_Answer_text eat ON ea.Element_Answer_text_ID = eat.ID
;

Answer 2

First, I'll say that SELECT * is frowned upon for production code; if you're just testing or running a user query periodically I can overlook this, but if this will be part of a stored procedure or something lasting, you should replace * with the list of columns you're looking for.

Second, you have a couple of options. You can exclude '%text%*4' like Michelle pointed out using WHERE NOT LIKE. If you're going to try to include it, you'll need to cast or convert the normal INT contents of the column to a string type like VARCHAR. That should allow the '%text%*4' to come through in the results. The other way which would retrieve the linked comment you're looking for could look something like this:

SELECT a.ID, a.Context, tld.TaskID, tld.createdby, a.FormID, fe.ElementID, CASE WHEN LEFT(ea.AnswerID,7) = '\%text\%*' THEN eat.Element_Answer_Text ELSE ea.AnswerID
FROM dbo.Answer a
INNER JOIN dbo.tasklist_data tld ON a.source_taskID = tld.TaskID
INNER JOIN dbo.Member m ON tld.createdby = m.uid
INNER JOIN dbo.Form f ON a.FormID = f.FormID
INNER JOIN dbo.Element_Answer ea ON a.ID = ea.AnswerID
INNER JOIN dbo.Form_Elements fe ON ea.ElementID = fe.ElementID
INNER JOIN dbo.Element_Answer_Text eat on eat.ID = RIGHT(ea.AnswerID,1)