Cast & Case

When I run the following query on its own it runs perfectly:

Select	h.branch as BRANCH, h.HHNUM,
	CAST(CASE WHEN h.CSDIR_DEP_FL = 'Y'	THEN 1 ELSE 0 END as smallint)as Direct_Deposit_Flag
	from	Touche.dbo.HHSUMM h
	where	h.PERCHECKSRV	>	0

When I add it to a much longer query (using a lot of Cast/Case) it gives me the arithmethic error - is there a better way to write this query or can it be when performing this calc multiple times it fails: full query below:

select m.BRANCH as Branch,
COUNT(m.HHNUM) as Total_HHs,
SUM(m.TOTALACCTS) as Total_Accounts,
(SUM(m.TOTALACCTS) / COUNT(m.HHNUM)) as Accounts_per_HH,
(SUM(m.TOTALSERV) / COUNT(m.HHNUM)) as Services_per_HH,
(SUM(m.AGE) / COUNT(m.HHNUM)) as Average_HH_Age,
SUM(s.SINGLE_SERVICE_HHs) as Single_Service_HHs,
SUM(s.TWO_SERVICE_HHs) as Two_Service_HHs,
SUM(s.THREE_SERVICE_HHs) as Three_Service_HHs,
SUM(s.FOUR_SERVICE_HHs) as Four_Service_HHs,
SUM(m.DEPOSITACC) as Total_Deposit_Accounts,
SUM(m.DEPOSITBAL) as Total_Deposit_Balance,
SUM(m.DEPOSITBAL) / COUNT(m.HHNUM) as Average_HH_Deposit_Balance,
SUM(m.LOANACC) as Total_Loan_Accounts,
SUM(m.LOANBAL) as Total_Loan_Balance,
SUM(m.LOANBAL) / COUNT(m.HHNUM) as Average_HH_Loan_Balance,
SUM(c.Personal_Checking_Accounts) as Personal_Checking_Accounts,
SUM(o.Online_Banking_Flag) as Online_Banking_HHs,
SUM(oc.Online_Banking_Flag) as Checking_HHs_Online_Banking,
SUM(dc.Debit_Card_Flag) as Checking_HHs_Debit_Card,
SUM(cast(dd.Direct_Deposit_Flag as int)) as Checking_HHs_Direct_Deposit,
--CASE WHEN m.CRMSTRATEGY = 'CULTIVATE' THEN COUNT(m.HHNUM) ELSE '' END as CULTIVATE_HHs,
--CASE WHEN m.CRMSTRATEGY = 'RETAIN' THEN COUNT(m.HHNUM) ELSE '' END as RETAIN_HHs,
--CASE WHEN m.CRMSTRATEGY = 'OPTIMIZE' THEN COUNT(m.HHNUM) ELSE '' END as OPTIMIZE_HHs,
--CASE WHEN m.CRMSTRATEGY = 'MAINTAIN' THEN COUNT(m.HHNUM) ELSE '' END as MAINTAIN_HHs,
--CASE WHEN LEFT(m.DEPDECILE_HH,2) = '01' THEN COUNT(m.HHNUM)
--WHEN LEFT(m.DEPDECILE_HH,2) = '02' THEN COUNT(m.HHNUM) ELSE '' END as DEPDECILE_LEVEL
--GENERATIONHH as Generation, DEPDECILE_HH as Deposit_Decile, LNDECILE_HH as Loan_Decile
'07/01/2015' as Date_Updated
from Touche.dbo.HHSUMM m,
(Select h.branch as BRANCH, h.HHNUM,
CASE
WHEN h.TOTALSERV = 1 THEN 1 ELSE 0 END as SINGLE_SERVICE_HHs,
CASE
WHEN h.TOTALSERV = 2 THEN 1 ELSE 0 END as TWO_SERVICE_HHs,
CASE
WHEN h.TOTALSERV = 3 THEN 1 ELSE 0 END as THREE_SERVICE_HHs,
CASE
WHEN h.TOTALSERV >= 4 THEN 1 ELSE 0 END as FOUR_SERVICE_HHs
from Touche.dbo.HHSUMM h) as s,
(Select h.branch as BRANCH, h.HHNUM,
CASE WHEN h.PERCHECKSRV >0 THEN 1 ELSE 0 END as Personal_Checking_Accounts
from Touche.dbo.HHSUMM h) as c,
(Select h.branch as BRANCH, h.HHNUM,
CAST(CASE WHEN h.CSHOMEBKG_FL = 'Y' THEN 1 ELSE 0 END as smallint)as Online_Banking_Flag
from Touche.dbo.HHSUMM h
where h.PERCHECKSRV > 0) as oc,
(Select h.branch as BRANCH, h.HHNUM,
CAST(CASE WHEN h.CSHOMEBKG_FL = 'Y' THEN 1 ELSE 0 END as smallint) as Online_Banking_Flag
from Touche.dbo.HHSUMM h) as o,
(Select h.branch as BRANCH, h.HHNUM,
CAST(CASE WHEN h.CSDBT_FL = 'Y' THEN 1 ELSE 0 END as smallint)as Debit_Card_Flag
from Touche.dbo.HHSUMM h
where h.PERCHECKSRV > 0) as dc,
(Select h.branch as BRANCH, h.HHNUM,
CAST(CASE WHEN h.CSDIR_DEP_FL = 'Y' THEN 1 ELSE 0 END as smallint)as Direct_Deposit_Flag
from Touche.dbo.HHSUMM h
where h.PERCHECKSRV > 0) as dd
where m.HHNUM = s.HHNUM
and m.HHNUM = c.HHNUM
and s.HHNUM = c.HHNUM
and m.BRANCH = s.BRANCH
and m.BRANCH = c.BRANCH
and s.BRANCH = c.BRANCH
and m.HHNUM = oc.HHNUM
and s.HHNUM = oc.HHNUM
and c.HHNUM = oc.HHNUM
and m.BRANCH = oc.BRANCH
and s.BRANCH = oc.BRANCH
and c.BRANCH = oc.BRANCH
and m.HHNUM = o.HHNUM
and s.HHNUM = o.HHNUM
and c.HHNUM = o.HHNUM
and oc.HHNUM = o.HHNUM
and m.BRANCH = o.BRANCH
and s.BRANCH = o.BRANCH
and c.BRANCH = o.BRANCH
and oc.BRANCH = o.BRANCH
and m.HHNUM = dc.HHNUM
and s.HHNUM = dc.HHNUM
and c.HHNUM = dc.HHNUM
and oc.HHNUM = dc.HHNUM
and o.HHNUM = dc.HHNUM
and m.BRANCH = dc.BRANCH
and s.BRANCH = dc.BRANCH
and c.BRANCH = dc.BRANCH
and oc.BRANCH = dc.BRANCH
and o.BRANCH = dc.BRANCH
and m.HHNUM = dd.HHNUM
and s.HHNUM = dd.HHNUM
and c.HHNUM = dd.HHNUM
and oc.HHNUM = dd.HHNUM
and o.HHNUM = dd.HHNUM
and dc.HHNUM = dd.HHNUM
and m.BRANCH = dd.BRANCH
and s.BRANCH = dd.BRANCH
and c.BRANCH = dd.BRANCH
and oc.BRANCH = dd.BRANCH
and o.BRANCH = dd.BRANCH
and dc.BRANCH = dd.BRANCH
group by m.BRANCH
order by 1

CAST is as int rather than smallint:

CAST(CASE WHEN h.CSDIR_DEP_FL = 'Y' THEN 1 ELSE 0 END as int)as Direct_Deposit_Flag

Unfortunately when used in the larger query it still does not work. When run on its own it is fine. My guess is that server is running out of calculation space etc.

Not sure what " running out of calculation space " means, but if you mean disk space (say, in tempdb), you wouldn't get an error about arithmetic. OTOH maybe you are overflowing some column or intermediate result. If that's the case, you need a bigger data type (like int instead of small int, as Scott suggested. Maybe you need bigint or even a decimal(38,0)

Might I suggest optimizing/simplying you query:

select Branch
      ,count(hhnum) as Total_HHs
      ,sum(totalaccts) as Total_Accounts
      ,sum(totalaccts)/count(hhnum) as Accounts_per_HH
      ,sum(totalserv)/count(hhnum) as Services_per_HH
      ,sum(age)/count(hhnum) as Average_HH_Age
      ,sum(case when totalserv=1 then 1 else 0 end) as Single_Service_HHs
      ,sum(case when totalserv=2 then 1 else 0 end) as Two_Service_HHs
      ,sum(case when totalserv=3 then 1 else 0 end) as Three_Service_HHs
      ,sum(case when totalserv=4 then 1 else 0 end) as Four_Service_HHs
      ,sum(depositacc) as Total_Deposit_Accounts
      ,sum(depositbal) as Total_Deposit_Balance
      ,sum(depositbal)/count(hhnum) as Average_HH_Deposit_Balance
      ,sum(loanacc) as Total_Loan_Accounts
      ,sum(loanbal) as Total_Loan_Balance
      ,sum(loanbal)/count(hhnum) as Average_HH_Loan_Balance
      ,sum(case when perchecksrv>0       then 1 else 0 end) as Personal_Checking_Accounts
      ,sum(case when cshomebkg_fl='Y'    then 1 else 0 end) as Online_Banking_HHs
      ,sum(case when cshomebkg_fl='Y'    then 1 else 0 end) as Checking_HHs_Online_Banking
      ,sum(case when csdbt_fl='Y'        then 1 else 0 end) as Checking_HHs_Debit_Card
      ,sum(case when csdir_dep_fl='Y'    then 1 else 0 end) as Checking_HHs_Direct_Deposit
      --,case when crmstrategy='CULTIVATE' then count(hhnum) else null end as CULTIVATE_HHs
      --,case when crmstrategy='RETAIN'    then count(hhnum) else null end as RETAIN_HHs
      --,case when crmstrategy='OPTIMIZE'  then count(hhnum) else null end as OPTIMIZE_HHs
      --,case when crmstrategy='MAINTAIN'  then count(hhnum) else null end as MAINTAIN_HHs
      --,case when left(depdecile_hh,2) in ('01','02) then count(hhnum) else null end as DEPDECILE_LEVEL 
      --,generationhh as Generation
      --,depdecile_hh as Deposit_Decile
      --,lndecile_hh as Loan_Decile
      ,'07/01/2015' as Date_Updated 
  from touche.dbo.hhsumm
 group by branch
 order by branch
1 Like