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?

Answers


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;

Need Your Help

Password protecting and only allowing one IP address to access a directory?

apache security .htaccess

I have a directory on my website that I need to make sure no one but myself can get into. From the reading I've done, it looks like there are two ways to protect a directory:

Facebook share button issue

facebook button share

I added the facebook share button within an email body (.htm). The purpose of the button is to share the photo a user received in his email. When the button is clicked, a new tab opens with the "Wr...