CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alex Gordon
Alex Gordon

Reputation: 60691

Conversion failed when converting the nvarchar value 'some name to data type int.

When i run my entire query I am getting this error message:

------------------------------------
--POPULATE SECTIONAAVGTAT-----------
------------------------------------
;with SpecimenDetail as (
select
s.*
,a.[mlis id]
,a.director
,a.rm
,a.rep
,a.css
,a.css2
,dbo.networkdays(s.[date received],GETDATE())-1 [Days On Hold]
,(case when dbo.networkdays(s.[date received],GETDATE())-1>=7 then '7+' 
   when dbo.networkdays(s.[date received],GETDATE())-1 in (5,6) then '5-6'
   when dbo.networkdays(s.[date received],GETDATE())-1 in (3,4) then '3-4'
   when dbo.networkdays(s.[date received],GETDATE())-1 in (1,2) then '1-2'
   when dbo.networkdays(s.[date received],GETDATE())-1=0 then '0'
  else 'na'
end) as [Days on Hold Group]
 from specimendetailtmp s
left join sectionaalignment a
on s.[mlis practice id]=a.[mlis id]
where s.[date distributed] is not null
and s.[date received]>='20121112'
and s.sectiona='not marked'
)


,

AvgTAT as
(
select 'director' emptype,director employee, cast(round(AVG(cast (dbo.networkdays(s.[date received],s.[date distributed])-1  as float)), 3, 1) as decimal(10,1)) AvgTAT,month(s.[date received]) month from SpecimenDetail s
where s.[date distributed] is not null
group by director,month(s.[date received])


union all

select 'rm' emptype,rm employee, cast(round(AVG(cast (dbo.networkdays(s.[date received],s.[date distributed])-1  as float)), 3, 1) as decimal(10,1))AvgTAT,month(s.[date received]) month from SpecimenDetail s
where s.[date distributed] is not null
group by rm,month(s.[date received])

union all

select 'rep' emptype,rep employee,cast(round(AVG(cast (dbo.networkdays(s.[date received],s.[date distributed])-1 as float)), 3, 1) as decimal(10,1)) AvgTAT,month(s.[date received]) month from SpecimenDetail s
where s.[date distributed] is not null
group by rep,month(s.[date received])

union all

select 'css' emptype,css employee,cast(round(AVG(cast (dbo.networkdays(s.[date received],s.[date distributed])-1  as float)), 3, 1) as decimal(10,1)) AvgTAT,month(s.[date received]) month from SpecimenDetail s
where s.[date distributed] is not null
group by css,month(s.[date received])

union all

select 'css2' emptype,css2 employee,cast(round(AVG(cast (dbo.networkdays(s.[date received],s.[date distributed])-1  as float)), 3, 1) as decimal(10,1)) AvgTAT,month(s.[date received]) month from SpecimenDetail s
where s.[date distributed] is not null
group by css2,month(s.[date received])
)

select * from avgtat

however if i run any of the inner selects, everything works fine!

why am i getting this error only when i execute the entire script?

Upvotes: 0

Views: 4466

Answers (1)

bobs
bobs

Reputation: 22184

I suspect that one or more of the SELECT clauses in the UNION ALL segments is producing results that implicitly convert to int. And, the other SELECT clauses return results that are nvarchar. The implicit conversion can occur when column values in the results are all integers.

If you don't know which column is causing the problem, you should review the results of each SELECT and determine if there's a common pattern (e.g., all values are integers in a nvarchar column.) Then, when you know the column that's generating the problem, use CAST or CONVERT to explicitly cast the value to the desired data type.

Upvotes: 2

Related Questions