Hi,
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)