When taking union of two tables with VARCHAR columns, the resulting query expects type INT?
Over the past six months, two large views with nearly 100 columns have been created at an office where I work. My SQL server is MS SQL Server 2008.
The two views pull data from two sources with two very different formats, so it isn't very practical to write it as a single view. However, it is necessary to query both views at once, so I wrote a simple query:
CREATE VIEW Combined_View AS SELECT * FROM View0 UNION SELECT * FROM View1;
It sounds like a simple, solution, but I'm having a problem now.
Combined_View has a column called Special. Up until today, View0.Special was always an empty string, while View1.Special is a nullable string with a few textual values.
I received instructions to modify View0.Special so that it would contain data from another table. Simple, right? Wrong! After making the requested modification, and trying to select from the combined view, I see an error that says, "Conversion failed when converting the varchar value 'XXXXXXXXXXXXXX' to data type int."
The above doesn't make any sense at all to me. I can't understand how I could have told SQL to convert this value to an integer, and yes, I did check that the value occurs in View0.Special.
Trying to track down the problem, I queried INFORMATION_SCHEMA.COLUMNS WITH
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'View%' AND COLUMN_NAME = 'Special';
The results of this query are strange.
View0.Special is VARCHAR(40) NOT NULL.
View1.Special is VARCHAR(20) NULL.
Combined_View.Special is INT NOT NULL.
I read this to indicate that SQL somehow inferred that the UNION of two different columns should have type INT, but why?
Please make sure that the ordinal position of columns in both views line-up. I suspect this is what's happening (just using 3 columns to illustrate my point)
CREATE VIEW Combined_View AS SELECT ,col1 ,Special ,col3 FROM View0 UNION SELECT Special ,col1 ,col3 FROM View1;
Now, if col1 & col3 are of int type, the above query is trying to fit view1.special into view0.col1 and causes issues with conversion. By asking you to make sure the columns line up correctly, what I mean is this:
CREATE VIEW Combined_View AS SELECT ,col1 ,Special ,col3 FROM View0 UNION SELECT col1 ,Special ,col3 FROM View1;