• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

COnversion from varchar to int

I am trying to sum up numbers afor a report and it was working with the ocassional dividee by zero error wouold throw it.  So I put in the CASE Statements to get around the dvide by zero errors but now I get this, it was working before I put in the case statments.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '%' to data type int.

What am I doing wrong and is there a better way to get around divide by zero errors
Select	' ( ' + strUIC + ' )  ' + strUname UNIT, 
		intreq REQ, 
		intAsgn ASGN, 
		CASE When intREQ = 0 then 100 ELSE Cast(CAST((intAsgn / intreq) * 100 as Integer)as varchar(4)) + '%' END PASGN, 
		intOS OS, 
		intOSA OSA, 
		CASE WHEN intOSA = 0 THEN 0 ELSE Cast(CAST((intOSA / intOS) * 100 as Integer) as varchar(4)) + '%' end POSA, 
		intTASGN TASGN, 
		CASE WHEN intTAsgn = 0 THEN 0 ELSE Cast(CAST((intTASGN / intreq) * 100 as integer) as varchar(4)) + '%' END PTASGN, 
		CASE WHEN intDMOSQ = 0 THEN 0 ELSE CAST(Cast((intDMOSQ / intTASGN) * 100 as integer)as varchar(4)) + '%' END DMOSQ, 
		CASE WHEN intDeploy = 0 THEN 0 ELSE CAST(Cast((intDeploy / intTASGN) * 100 as integer)as varchar(4)) + '%' END Deploy, 
		intDental Dental, intMedical Medical, intAdmin Admin from tblReadinessUnitReport where strRptCode like 'AADHA%'
UNION ALL 
SELECT	'TOTALS', 
		SUM(intREQ) REQ, 
		SUM(intAsgn) ASGN, 
		CASE When SUM(intREQ) = 0 then '100' ELSE CAST(CAST(SUM(intAsgn) / SUM(intReq) * 100 as integer) as varchar(6)) + '%' END PASGN, 
		SUM(intOS)OS, SUM(intOSA) OSA, 
		CASE WHEN SUM(intOSA)= 0 THEN '0' ELSE Cast(CAST((SUM(intOSA) / SUM(intOS)) * 100 as Integer)as varchar(4)) + '%' end POSA, 
		SUM(intTAsgn) TASGN, 
		CASE WHEN SUM(intTasgn) = 0 THEN '0' ELSE Cast(CAST(((SUM(intTAsgn)) / SUM(intreq)) * 100 as integer) as varchar(4)) + '%' END PTASGN, 
		CASE WHEN SUM(intDMOSQ) = 0 THEN '0' ELSE CAST(Cast((SUM(intDMOSQ) / SUM(intTAsgn)) * 100 as integer)as varchar(4)) + '%' END DMOSQ, 
		CASE WHEN SUM(intDeploy) = 0 THEN '0' ELSE CAST(Cast((SUM(intDeploy) / SUM(intTAsgn)) * 100 as integer)as varchar(4)) + '%' END Deploy, 
		SUM(intDental) Dental, SUM(intMedical) Medical, SUM(intADmin) Admin from tblReadinessUnitReport where strRptCode like 'AADHA%'

Open in new window

0
kdeutsch
Asked:
kdeutsch
1 Solution
 
Scott PletcherSenior DBACommented:
CASE When intREQ = 0 then 100 ELSE Cast(CAST((intAsgn / intreq) * 100 as Integer)as varchar(4)) + '%' END PASGN,
            intOS OS,

Should be:

CASE When intREQ = 0 then '100' ELSE Cast(CAST((intAsgn / intreq) * 100 as Integer)as varchar(4)) + '%' END PASGN,
            intOS OS,

etc.

0
 
dougaugCommented:
Why are you concatenating '%' symbol to the expression? If you remove it maybe it will run ok
0
 
kdeutschAuthor Commented:
ScottPletcher:

I originally had it this way, but then I go back to the divide by zero error on that same line, some of the fields have a zero for intReq and I thought this would get around it but as soon as I put the tick marks back intot he case statment i get the divide by zero error agian.
0
 
kdeutschAuthor Commented:
Ok got it to work, had to change another field to look for zero.
0
 
Shaun KlineLead Software EngineerCommented:
Are you correcting all of the lines that you perform the division, or just the one line mentioned?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now