Syntax on nested ifs

[SACRob]

I am trying to check if a value is null befoer i run a function but i also need to verify if a city equals federal way. the query without this entry gives me about 5650 rows and with this line i only get about 5100 rows and then an error

Msg 8114, Level 16, State 5, Line 60
Error converting data type varchar to numeric.

,case
when hld.firstchgtme is null then 'No Response'
else
case 
when (c.city='Federal Way') then dbo.fn_workinghourfed(hld.createtme, hld.firstchgtme)
else dbo.fn_workinghour(hld.createtme, hld.firstchgtme)
end
end
as 'Response Time'

Line 60 is the first line in this select.

Select c.CUSTNMBR,c.CALLNBR,c.Customer_Reference, c.SRVTYPE, c.SRVSTAT
, '"'+c.SVCDESCR+'"', c.OFFID, c.TECHID, c.ENTDTE +' '+ c.ENTTME as 'Entry'
, c.ETADTE +' '+c.ETATME AS 'ETA', c.ARRIVDTE+ ' ' + c.ARRIVTME AS 'Arrival'
, c.ADRSCODE, '"'+c.ADDRESS1+'"', c.CITY, c.State, c.ZIP, c.CNTCPRSN, mes.TXTFIELD as 'WO Closing Comments'
,case
when hld.firstchgtme is null then 'No Response'
else
case 
when (c.city='Federal Way') then dbo.fn_workinghourfed(hld.createtme, hld.firstchgtme)
else dbo.fn_workinghour(hld.createtme, hld.firstchgtme)
end
end
as 'Response Time'
,hld.workdur as 'Rslv Time',hld.holddur as 'Hold Time',((hld.workdur+hld.holddur)*.01667) as 'WO Hours', code.PROBCDE, code.CAUSECDE, code.RPRCODE
from  SLRCALC hld
INNER JOIN SVC00200 c on c.CALLNBr=hld.CALLNBR
INNER JOIN SVC00201 code ON c.CALLNBR=code.CALLNBR
INNER JOIN SY03900 mes ON mes.NOTEINDX=c.NOTEINDX

 

[gmmastros]

[red]Error converting data type varchar to numeric.[/red]

The error message happens when you store a number in to a varchar column.

If I had to guess, I would suggest that your bad data is in workdur or holddur. To find out...

[tt][blue]
Select workdur
from SLRCALC
Where IsNumeric(workdur) = 0
[/blue][/tt]

[tt][blue]
Select holddur
from SLRCALC
Where IsNumeric(holddur) = 0
[/blue][/tt]


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom