Error converting data type varchar

Question

I currently have a table with a column as varchar. This column can hold numbers or text. During certain queries I treat it as a bigint column (I do a join between it and a column in another table that is bigint)

As long as there were only numbers in this field had no trouble but the minute even one row had text and not numbers in this field I got a "Error converting data type varchar to bigint." error even if in the WHERE part I made sure none of the text fields came up.

To solve this I created a view as follows:

SELECT     TOP (100) PERCENT ID, CAST(MyCol AS bigint) AS MyCol
FROM         MyTable
WHERE     (isnumeric(MyCol) = 1)

But even though the view shows only the rows with numeric values and casts Mycol to bigint I still get a Error converting data type varchar to bigint when running the following query:

SELECT * FROM MyView where mycol=1

When doing queries against the view it shouldn't know what is going on behind it! it should simply see two bigint fields! (see attached image, even mssql management studio shows the view fields as being bigint)

Answer 1

OK. I finally created a view that works:

SELECT TOP (100) PERCENT id, CAST(CASE WHEN IsNumeric(MyCol) = 1 THEN MyCol ELSE NULL END AS bigint) AS MyCol
FROM         dbo.MyTable
WHERE     (MyCol NOT LIKE '%[^0-9]%')

Thanks to AdaTheDev and CodeByMoonlight. I used your two answers to get to this. (Thanks to the other repliers too of course)

Now when I do joins with other bigint cols or do something like 'SELECT * FROM MyView where mycol=1' it returns the correct result with no errors. My guess is that the CAST in the query itself causes the query optimizer to not look at the original table as Christian Hayter said may be going on with the other views

Answer 2

Ideally, you want to try to avoid storing the data in this form - would be worth splitting the BIGINT data out in to a separate column for both performance and ease of querying.

However, you can do a JOIN like this example. Note, I'm not using ISNUMERIC() to determine if it's a valid BIGINT because that would validate incorrect values which would cause a conversion error (e.g. decimal numbers).

DECLARE @MyTable TABLE (MyCol VARCHAR(20))
DECLARE @OtherTable TABLE (Id BIGINT)

INSERT @MyTable VALUES ('1')
INSERT @MyTable VALUES ('Text')
INSERT @MyTable VALUES ('1 and some text')
INSERT @MyTable VALUES ('1.34')
INSERT @MyTable VALUES ('2')
INSERT @OtherTable VALUES (1)
INSERT @OtherTable VALUES (2)
INSERT @OtherTable VALUES (3)

SELECT *
FROM @MyTable m
    JOIN @OtherTable o ON CAST(m.MyCol AS BIGINT) = o.Id
WHERE m.MyCol NOT LIKE '%[^0-9]%'

Update: The only way I can find to get it to work for having a WHERE clause for a specific integer value without doing another CAST() on the supposedly bigint column in the where clause too, is to use a user defined function:

CREATE  FUNCTION [dbo].[fnBigIntRecordsOnly]()
RETURNS @Results TABLE (BigIntCol BIGINT)
AS
BEGIN
INSERT @Results
SELECT CAST(MyCol AS BIGINT)
FROM MyTable
WHERE MyCol NOT LIKE '%[^0-9]%'
RETURN
END

SELECT * FROM [dbo].[fnBigIntRecordsOnly]() WHERE BigIntCol = 1

I don't really think this is a great idea performance wise, but it's a solution

Answer 3

To answer your question about the error message: when you reference a view name in another query (assuming it's a traditional view not a materialised view), SQL Server effectively does a macro replacement of the view definition into the consuming query and then executes that.

The advantage of doing this is that the query optimiser can do a much better job if it sees the whole query, rather than optimising the view separately as a "black box".

A consequence is that if an error occurs, error descriptions may look confusing because the execution engine is accessing the underlying tables for the data, not the view.

I'm not sure how materialised views are treated, but I would imagine that they are treated like tables, since the view data is cached in the database.

Having said that, I agree with previous answers - you should re-think your table design and separate out the text and integer data values into separate columns.

Answer 4

Try changing your view to this :

SELECT TOP 100 PERCENT ID, 
Cast(Case When IsNumeric(MyCol) = 1 Then MyCol Else null End AS bigint) AS MyCol
FROM MyTable
WHERE (IsNumeric(MyCol) = 1)