conversion failed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

conversion failed

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-22 : 13:33:53

When I run this I get on the bold part below
Conversion failed when converting the varchar value 'Area 1' to data type int.


SELECT ''   AS reg, RegionAcronym, RegionAcronym  AS area , 
       SUM(totpenfodds)      AS 'totpenfodds', 
       SUM(pendinfo) AS 'pendinfo',
       SUM(pendndds)       AS 'pendndds', 
       SUM(pendndds1)       AS 'pendndds1', 
       SUM(pendfo300)      AS 'pendfo300', 
       SUM(penddds300)       AS 'penddds300', 
       SUM(pend250)  AS 'pend250',
		SUM(pend300) AS 'pend300',
		SUM(pend351)  AS 'pend351',
		SUM(pend400) AS 'pend400'
FROM   T16Report 
where reg='c'
group by reg, RegionAcronym
union
select '' as reg, RegionAcronym, Area,'Area ' + right (area,1),       
      SUM(totpenfodds)      AS 'totpenfodds', 
       SUM(pendndds)       AS 'pendndds', 
       SUM(totpenfodds - pendndds) AS pendinfo,
       SUM(pendfo300)      AS 'pendfo300', 
       SUM(penddds300)       AS 'penddds300', 
       SUM(pend250)  AS 'pend250',
		SUM(pend300) AS 'pend300',
		SUM(pend351)  AS 'pend351',
		SUM(pend400) AS 'pend400'
FROM   T16Report 
where reg='c' and area='01'
group by reg, RegionAcronym, area

The first query gives me this:


Reg  RegionAcronym  Area       Totpenfodds  etc... 
	PHI	     PHI	69633	  


The Second query gives me this: 

Reg  RegionAcronym  Area  no column name  totpenfodds etc...
	PHI	    01	    Area 1	   13896

It's having problems with the Area 1 name. I don't know how to cast or covert this:

'Area ' + right (area,1). How do I do that?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-02-22 : 14:07:18

Union requires that the columns be the same. For whatever reason you have added an extra column ('Area ' + right (area,1)) in the second query that is in the same ordinal position as (SUM(totpenfodds) AS 'totpenfodds') in the first query:

http://msdn.microsoft.com/en-us/library/ms180026.aspx

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-22 : 14:19:35

Thanks I didn't even realize that. I got it to work by adding the correct columns to each query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources