CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
cnd
cnd

Reputation: 33714

SQL weird error : Error converting value

When I do

SELECT RecTime, CAST(ID as nvarchar(MAX)) AS Col, InitialValue FROM [dbo].[Changes]

I've got no errors

When I do

SELECT RecTime, Col, InitialValue FROM [dbo].[XDeltaIntervals]

I've got no errors

But when I do

SELECT RecTime, CAST(ID as nvarchar(MAX)) AS Col, InitialValue 
FROM [dbo].[Changes]
        UNION ALL
   SELECT RecTime, Col, InitialValue FROM [dbo].[XDeltaIntervals]

Col from XDeltaIntervals is nvarchar(MAX)

I've got Error Message :

Message 245, Level 16, State 1, Line 2 Error converting value nvarchar "davl> 40" to the data type bit. Attention! NULL is excluded in the aggregate, or other operations SET.

davl> 40 is Col from XDeltaIntervals and it's nvarchar and I can't understand where I'm trying to convert it to bit :S

How can I avoid this request to convert Col from second part of union to bit ?

Upvotes: 1

Views: 818

Answers (3)

Martin Smith
Martin Smith

Reputation: 452977

Attention! NULL is excluded in the aggregate, or other operations SET. doesn't appear to have any correlation to the query you have shown. Are either of these Views?

If so try EXEC sp_refreshview 'dbo.XDeltaIntervals'

Upvotes: 1

Tim
Tim

Reputation: 5421

It looks as though the > character in the actual column-name for "col" is being interpreted as an operator. What if you wrap the column-name in brackets [].

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40289

Have you tried reversing the order of the UNION?

SELECT RecTime, Col, InitialValue
FROM [dbo].[XDeltaIntervals] 
UNION ALL
SELECT RecTime, CAST(ID as nvarchar(MAX)) AS Col, InitialValue
FROM [dbo].[Changes]

The data types of the resulting set are based on the values from the first SELECT statement.

Upvotes: 1

Related Questions