sql server - Getting error: Msg 8134, Level 16, State 1, Line 1 -

sql server - Getting error: Msg 8134, Level 16, State 1, Line 1 -

i have reviewed other replies problem, cannot find appropriate response situation. trying split "actual hours" "estimated hours". however, estimated hours can null (no entry) or zero. actual hours can zero. when partition statement, can't have estimated hours null or actual hours zero. "coalesce" statements don't appear helping. advice helpful.

select puv.projectname, puv.[project id], puv.[project director], puv.[project owner (manager)], puv.[project type1], **coalesce( puv.[estimated hours], 0 ) esthours ,puv.projectstatusdate, puv.projectstartdate, puv.projectbaseline0startdate, puv.projectactualstartdate, puv.projectstartvariance, puv.projectbaseline0finishdate ,puv.projectfinishdate, puv.projectfinishvariance, puv.projectactualfinishdate, puv.projectwork, puv.projectbaseline0work, puv.projectactualwork, puv.projectworkvariance, puv.projectremainingwork ,puv.[project phase], puv.[hold - canceled indicator], cwi.stagename, tb.tb_base_num, max (tb.created_date) recentbaseline ,case when puv.[estimated hours] null 0 end [estimated%] ,case when puv.projectactualwork <> 0 cast(coalesce(puv.projectactualwork,0) decimal(20,2) )/cast(coalesce(puv.[estimated hours],0)as decimal(20,2)) else 0 end [estimated%]* --datediff(day, puv.projectbaseline0finishdate, puv.projectfinishdate) msp_epmproject_userview puv left outer bring together ( select wsi.projectuid, wp.phasename, wp.phaseuid, ws.stagename, ws.stageuid msp_epmworkflowstage ws inner bring together msp_epmworkflowphase wp on ws.phaseuid = wp.phaseuid inner bring together msp_epmworkflowstatusinformation wsi on ws.stageuid = wsi.stageuid , wsi.stageentrydate not null , (wsi.stagestatus != 0 , wsi.stagestatus != 4) ) cwi on puv.projectuid = cwi.projectuid bring together sps_ppm_it_published.dbo.msp_task_baselines tb on puv.projectuid = tb.proj_uid tb.tb_base_num = 0 grouping puv.projectname, puv.[project id], puv.[project director], puv.[project owner (manager)], puv.[project type1], puv.[estimated hours] ,puv.projectstatusdate, puv.projectstartdate, puv.projectbaseline0startdate, puv.projectactualstartdate, puv.projectstartvariance, puv.projectbaseline0finishdate ,puv.projectfinishdate, puv.projectfinishvariance, puv.projectactualfinishdate, puv.projectwork, puv.projectbaseline0work, puv.projectactualwork, puv.projectworkvariance, puv.projectremainingwork ,puv.[project phase], puv.[hold - canceled indicator], cwi.stagename, tb.tb_base_num order puv.projectname

your title utterly unrelated question , posting super long, aliased query database not great way help.

luckily, curious plenty see whether "msg-8134-level-16-state-1-line-1" secret message mars checked out.

anyway, here's little illustration of how utilize case statement avoid partition zero:

select case when example.estimated_hours != 0 , example.estimated_hours not null 100 / example.estimated_hours else 0 end ratio ( select estimated_hours ( values (0), (1), (null), (2), (3)) mock_data (estimated_hours)) example;

you can copy/paste sql terminal , run see output:

ratio ------- 0 100 0 50 33 (5 rows)

sql-server tsql divide-by-zero