Link to home
Create AccountLog in
Avatar of Trudye
TrudyeFlag for United States of America

asked on

Conversion failed when converting the varchar value 'TEN' to data type int.

I am recieving the following error when executing the following code. Field MonData is nvarchar (80), the only integer feild in the concatination is @Cnt all other fields are varchar or nvarchar. If I remove the everything past space(22) it works fine. I double checked the length and it does not exceed 80 bytes.
Can anyone see what I am doing wrong?

INSERT INTO tbl_Export            
Select DISTINCT TOP(1) space(32) + '9' + space(2) + @Cnt + space(2) + Sys + space(2) +
      Prin + space(22) + 'TEN' + space(1) + 'ZZ' As MonData
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin      

Conversion failed when converting the varchar value 'TEN' to data type int.
Avatar of Otana
Otana

Try this:

INSERT INTO tbl_Export            
Select DISTINCT TOP(1) space(32) + '9' + space(2) + cast(@Cnt as varchar(100)) + space(2) + cast(Sys as varchar(100)) + space(2) +
      Prin + space(22) + 'TEN' + space(1) + 'ZZ' As MonData
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin      
Avatar of Trudye

ASKER

Thank you Otana for responding so quickly. I pasted in your code and recieved the following error:
Conversion failed when converting the varchar value '                                9  75000  ' to data type int.

The error value is referring to:
space(32) + '9' + space(2) + cast(@Cnt as varchar(100))

Avatar of auke_t
Try this:

INSERT INTO tbl_Export            
Select DISTINCT TOP(1) space(32) + '9' + space(2) + cast(@Cnt as varchar(100)) + space(2) + cast(Sys as varchar(100)) + space(2) +
     cast(Prin as varchar(100) + space(22) + 'TEN' + space(1) + 'ZZ' As MonData
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin    
Right, forgot to cast "Prin".

If you add int fields and varchar fields, SQL will try to convert the varchars into ints, which almost always results in errors. So you have to cast all ints as varchars, because when you add only varchars you'll put all strings together instead of trying to add mathematically.
Avatar of Trudye

ASKER

I changed my code to CAST everything as varchar or nvarchar. Since the output field was nvarchar. The problem is all I got was a null value in my output field.

Then I changed the output field (MonData) to varchar and all Cast all fields as varchar and I still got a Null value in the output field.

I also ran it with just @cnt, sys, prin cast and I still got a Null value in the output field.
But at least the error is gone (smile)
SOLUTION
Avatar of auke_t
auke_t
Flag of Netherlands image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Trudye

ASKER

I understand that I can use the COALESCE function to solve my problem. I'm just not sure how to structure it. The example in Help puts the COALESCE function inside the Cast function. But the example only uses on Cast function for all field values. Can I use one CAST func for all of my field values even with the Space func in between?
Avatar of Trudye

ASKER

I tried the Is Null with the Case statement because I found it in Help. But I got the same results, a null value in my output field.

INSERT INTO tblExport                   
Select DISTINCT TOP(1) space(32) + + '9' + space(2) +
           CASE When cast(@Cnt as varchar(100)) is Not null then cast(@Cnt as varchar(100)) END +
      space(2) +
          CASE WHEN cast(Sys as varchar(100)) IS NOT NUll then cast(Sys as varchar(100)) END +
      space(2) +
          CASE WHEN cast(Prin as varchar(100)) IS NOT NUll then cast(Prin as varchar(100)) END +
      space(22) +
           CASE WHEN (Term_id) IS NOT NULL THEN Term_id END +
      space(1) +
           CASE WHEN (Op_Code) IS NOT NULL THEN Op_Code END
      As  MONDATA
      FROM dbo.tblTrans
      WHERE Sys = @vwSys AND Prin = @vwPrin
Ok, you can use a case statement and IS NULL, but then you have to give an ELSE '' otherwise it will be NULL anyway ;-)
This should do the same, but it's syntax is shorter (and more readable)
INSERT INTO 
	tblExport                   
Select 
	DISTINCT TOP(1) 
	space(32) + 
	'9' + 
	space(2) + 
	ISNULL(cast(@Cnt as varchar(100)), '') + 
	space(2) + 
	ISNULL(cast(Sys as varchar(100)), '') + 
	space(2) + 
	ISNULL(cast(Prin as varchar(100)), '') + 
	space(22) + 
	ISNULL(Term_id), '') + 
	space(1) + 
	ISNULL(Op_Code), '') As MONDATA
FROM 
	dbo.tblTrans
WHERE 
	Sys = @vwSys 
AND 
	Prin = @vwPrin

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Trudye

ASKER


Thanks so much Auke, I finished the code yesterday, I figured out what the problem was. I like what you did also, so I'll try that next time I'm faced with this same problem.