Tell me what i did wrong - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Tell me what i did wrong

Author

Topic

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-22 : 10:05:03

I am getting this error: Conversion failed when converting the varchar value to data type int

select type,

COALESCE (analyst_bu.our_bu_name,a.BUSINESS_UNIT)[Analyst_bu],

a.location_descr,

coalesce(my_dept.our_dept,a.gl_home_department)[Analyst_dept],

a.mgr_last_name,

a.mgr_first_name,

a.last_name,

a.first_name,

coalesce(affected_bu.our_bu_name, b.business_unit)[Affected_bu],

time

from MCD.dbo.my_data

join [servername].TPS.dbo.my_users a on

analyst_email=a.email

left outer join [servername].TPS.dbo.my_users b

on affected_email=b.email

left outer join MCD.dbo.my_dept

ON a.gl_home_department = my_dept.ps_gl_dept

left outer join MCD.dbo.my_bus analyst_bu ON

a.BUSINESS_UNIT= analyst_bu.short_bu_name

left outer join MCD.dbo.my_bus affected_bu on

b.BUSINESS_UNIT= affected_bu.short_bu_name

UNION ALL

SELECT '' AS Expr4, COALESCE (analyst_bu.our_bu_name, a.BUSINESS_UNIT)[Analyst_bu],

a.location_descr, COALESCE (dbo.my_dept.our_dept, a.gl_home_department)[Analyst_dept], a.mgr_last_name, a.mgr_first_name, a.last_name,

a.first_name,time_spent,

(CASE WHEN bu_num = '1' THEN 'WTC' WHEN bu_num = '2' THEN 'WTC' WHEN bu_num = '3' THEN 'WBS' WHEN bu_num

= '4' THEN 'WBS' WHEN bu_num = '5' THEN 'EIT' WHEN bu_num = '6' THEN 'WAM' WHEN bu_num = '7' THEN 'WBS' WHEN

bu_num = '8' THEN 'ITC' WHEN bu_num = '9' THEN 'ITC' WHEN bu_num = '10' THEN 'WBS' WHEN bu_num = '11' THEN

'WIC' WHEN bu_num = '12' THEN 'ITC' WHEN bu_num = '20' THEN 'WBS' WHEN bu_num = '21' THEN 'WTC' WHEN bu_num

= '25' THEN 'TRDO' ELSE '' END) [Affected_bu]

FROM MCD.sd_incidents_olap_view LEFT OUTER JOIN

[server name].TPS.dbo.my_users AS a ON AHD.sd_incidents_olap_view.analyst_email = a.email INNER JOIN

MCD.net_res ON AHD.sd_incidents_olap_view.bu_num = AHD.net_res.nr_financial_id LEFT OUTER JOIN

dbo.my_dept ON a.gl_home_department = dbo.my_dept.ps_gl_dept LEFT OUTER JOIN

dbo.my_bus AS analyst_bu ON a.BUSINESS_UNIT = analyst_bu.short_bu_name

Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 10:08:02

quote:
Originally posted by sodeep

I am getting this error: Conversion failed when converting the varchar value to data type int

select type,

COALESCE (analyst_bu.our_bu_name,a.BUSINESS_UNIT)[Analyst_bu],

a.location_descr,

coalesce(my_dept.our_dept,a.gl_home_department)[Analyst_dept],

a.mgr_last_name,

a.mgr_first_name,

a.last_name,

a.first_name,

coalesce(affected_bu.our_bu_name, b.business_unit)[Affected_bu],

time

from MCD.dbo.my_data

join [servername].TPS.dbo.my_users a on

analyst_email=a.email

left outer join [servername].TPS.dbo.my_users b

on affected_email=b.email

left outer join MCD.dbo.my_dept

ON a.gl_home_department = my_dept.ps_gl_dept

left outer join MCD.dbo.my_bus analyst_bu ON

a.BUSINESS_UNIT= analyst_bu.short_bu_name

left outer join MCD.dbo.my_bus affected_bu on

b.BUSINESS_UNIT= affected_bu.short_bu_name

UNION ALL

SELECT '' AS Expr4, COALESCE (analyst_bu.our_bu_name, a.BUSINESS_UNIT)[Analyst_bu],

a.location_descr, COALESCE (dbo.my_dept.our_dept, a.gl_home_department)[Analyst_dept], a.mgr_last_name, a.mgr_first_name, a.last_name,

a.first_name,time_spent,

(CASE WHEN bu_num = '1' THEN 'WTC' WHEN bu_num = '2' THEN 'WTC' WHEN bu_num = '3' THEN 'WBS' WHEN bu_num

= '4' THEN 'WBS' WHEN bu_num = '5' THEN 'EIT' WHEN bu_num = '6' THEN 'WAM' WHEN bu_num = '7' THEN 'WBS' WHEN

bu_num = '8' THEN 'ITC' WHEN bu_num = '9' THEN 'ITC' WHEN bu_num = '10' THEN 'WBS' WHEN bu_num = '11' THEN

'WIC' WHEN bu_num = '12' THEN 'ITC' WHEN bu_num = '20' THEN 'WBS' WHEN bu_num = '21' THEN 'WTC' WHEN bu_num

= '25' THEN 'TRDO' ELSE '' END) [Affected_bu]

FROM MCD.sd_incidents_olap_view LEFT OUTER JOIN

[server name].TPS.dbo.my_users AS a ON AHD.sd_incidents_olap_view.analyst_email = a.email INNER JOIN

MCD.net_res ON AHD.sd_incidents_olap_view.bu_num = AHD.net_res.nr_financial_id LEFT OUTER JOIN

dbo.my_dept ON a.gl_home_department = dbo.my_dept.ps_gl_dept LEFT OUTER JOIN

dbo.my_bus AS analyst_bu ON a.BUSINESS_UNIT = analyst_bu.short_bu_name

Please help

Make sure all fields used inisde each COALESCE are of same datatype(it seems like you've used varchar & int fields within a single COALESCE and hence the error)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-22 : 10:09:05

It is difficult for us to help as we don't know the data type of the columns in the select statement.

Check that the columns of the same order are returning the same data type


select cola, colb, colc from table1
union all
select col1, col2, col3 from table2

cola and col1 must be of the same data type, colb and col2 etc, else it will be implicitly converted to the other and may cause the error you exprienced

KH
[spoiler]Time is always against us[/spoiler]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-22 : 10:12:21

Got the solution: Thanks:

I used Convert(varchar(10), column) on both queries

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources