Cannot convert nvarchar datatype to int error

Question

Cannot convert nvarchar datatype to int error

paul.starr

SSCrazy

Points: 2505
More actions
November 11, 2008 at 2:59 pm

#96721

when i run this stored procedure error is:
Conversion failed when converting the nvarchar value 'A' to data type int.
select A.Account_number, A.Customer_name, A.Known_As, A.Legacy_Account_Number, A.Account_Established_Date, CASE WHEN A.Customer_Status = 'I' THEN CAST ('0' AS int) ELSE CAST ('1' AS int) END, A.Account_Established_Date
from
dbo.AVW_11i_WA_BILLTO_Customers A
left outer join dbo.Customers C
on A.Account_number = C.CustomerID
where
C.CustomerID is null
GO

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Is A.Account_Number an NVarchar()? If so then you should CAST C.Customer_ID to NVarchar() in your ON clause.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Try this:

select A.Account_number, A.Customer_name, A.Known_As,

A.Legacy_Account_Number, A.Account_Established_Date,

CASE A.Customer_Status

WHEN 'I' THEN 0

ELSE 1

END,

A.Account_Established_Date

from

dbo.BILL_TO_Customers A

left outer join dbo.Customers C

on A.Account_number = C.CustomerID

where

C.CustomerID is null

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

paul.starr SSCrazy Points: 2505 More actions · Answer 3

it works but error is:

Conversion failed when converting the nvarchar value 'A' to data type int

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

This is actually a continuation of the following thread:

http://www.sqlservercentral.com/Forums/Topic600148-338-5.aspx#bm601093

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 5

paul.starr (11/11/2008)
it works but error is:
Conversion failed when converting the nvarchar value 'A' to data type int

So... what do you think an 'A' should convert to when converting to an INT? Or do you just want to ignore it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

If it is an 'I' then 0 else 1.

Jeff, been try to help him, but we can't even get him to help himself. I have pointed him toward your article on how to ask for help many times, but he just doesn't seem to get it.

paul.starr SSCrazy Points: 2505 More actions · Answer 7

I used a CAST function for the value

Case When A.Customer_Status = 'I' THEN CAST ('0' AS int) ELSE CAST('1' AS int) END

So status in the table should be updated to 1 if the value in the view is A, if it is I then it will update to 0

paul.starr SSCrazy Points: 2505 More actions · Answer 8

Lynn the create table statement was wrong, what about the datatypes, and how many value lines do you need

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

paul.starr (11/11/2008)
Lynn the create table statement was wrong, what about the datatypes, and how many value lines do you need

I've told you what I need. You need to read and follow the guidelines presented in the article below to help us help you. Nothing more, nothing less.

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 10

Lynn Pettis (11/11/2008)
If it is an 'I' then 0 else 1.
Jeff, been try to help him, but we can't even get him to help himself. I have pointed him toward your article on how to ask for help many times, but he just doesn't seem to get it.

Yep... thought I'd give it one more try. I'm moving on...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)