Sql-server – Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value ‘%text%4’ to data type int

sql serversql-server-2012

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.)

Best Answer

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
;

Related Solutions

Sql-server – Selecting the right data type for text

Data type - VARCHAR for ANSI which uses 1 byte per character or NVARCHAR for UNICODE which uses 2 bytes per character. In your case, I would suggest NVARCHAR, so you can store multiple languages within the same columns. Avoid TEXT and NTEXT as they are deprecated.

Searching - It is possible to use LIKE predicate, for simple searching with Wild Cards, but I suggest you investigate the use of Full Text indexing which will increase query performance and also allow you to use the FULLTEXT predicate for very flexible fuzzy searching.

Bullets and hyperlinks - I think you are thinking of SQL in the wrong way here. I think you should be thinking of those tasks within your client application. To facilitate this from the back end, hyperlinks I would split into a separate table with a key that you can then report on. For bullets, again strongly suggest controlling this through the client side, but if you really wanted to, you could insert a U+2022 at the beginning of each row. If there are multiple bullets per row of data, you could store this char at the beginning of each bullet and then use a string splitting function when reading from the table. Many ways of accomplishing this but suggest using XML sub query.

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Write your query this way. It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM 
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table. This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.

Best Answer

Related Solutions

Sql-server – Selecting the right data type for text

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Related Question