CODEWITHSUNDEEP
sqlsql-server-2008
Pindakaas
Pindakaas

Reputation: 4439

sql error: Conversion failed when converting the nvarchar

I am trying to run this query but is not working. I am getting an error. How to fix?:

Conversion failed when converting the nvarchar value 'Jon Yang ' to data type int.

sql

use adventureworks 
go 

select si.CustomerID, 
'myField' = 
            CASE 
                 When (Select Top 1 FirstName+ ' ' + LastName + ' ' + EmailPromotion   From Person.Contact pc Where si.ContactID = pc.contactid ) is not null Then  
            Cast((Select Top 1 FirstName+ ' ' + LastName  + ' ' + EmailPromotion From Person.Contact pc Where si.ContactID = pc.contactid  ) As varchar) 
            Else '' 
            END 
from Sales.Individual si 
where si.CustomerID=11000

Upvotes: 0

Views: 1119

Answers (2)

gbn
gbn

Reputation: 432210

The error occurs because of datatype precedence: nvarchar will be converted to int.

It is either on EmailPromotion or CustomerID so decide which line you want below. Also, no need to use an inline query.

select
    si.CustomerID, 
    'myField' = ISNULL(FirstName + ' ' + 
                       LastName  + ' ' + 
                       Cast(EmailPromotion AS nvarchar(100))
                       -- EmailPromotion ?
                       , '')
from
    Sales.Individual si 
    LEFT JOIN
    Person.Contact pc ON si.ContactID = pc.contactid
where
    si.CustomerID = N'11000'
    -- si.CustomerID = 11000 ?

TOP without ORDER BY is meaningless, so if you have multiple rows in Person.Contact for each row in Sales.Individual you'd need another construct...

Upvotes: 2

Jake Feasel
Jake Feasel

Reputation: 16955

One of the fields you're adding must be of type int - could it be the "EmailPromotion" field? In any case - that addition implies a conversion, but obviously you can't add a name to a number. Instead, try this:

use adventureworks 
go 

select si.CustomerID, 
'myField' = 
            CASE 
                 When (Select Top 1 FirstName+ ' ' + LastName + ' ' + cast(EmailPromotion   as nvarchar) From Person.Contact pc Where si.ContactID = pc.contactid ) is not null Then  
            Cast((Select Top 1 FirstName+ ' ' + LastName  + ' ' + cast(EmailPromotion   as nvarchar)  From Person.Contact pc Where si.ContactID = pc.contactid  ) As varchar) 
            Else '' 
            END 
from Sales.Individual si 
where si.CustomerID=11000

Upvotes: 0

Related Questions