title: Error: Arithmetic overflow error converting float to data type numeric
body: <p>I get an error when running a query.
  The error is:
  Arithmetic overflow error converting float to data type numeric.</p>
  
  <p>Code:</p>
  
  <pre><code>select g0.docnumber as docnumber
      , g0.datum
      , g0.crdnr
      , datepart(quarter, g0.datum) as quarter
      , g0.bkjrcode
      , g0.periode
      , sum(g0.[Toner Virgin]) as [Toner Virgin]
      , sum(g0.[Inkt Virgin]) as [Inkt Virgin]
      , sum(g0.[Non Virgin]) as [Non Virgin]
      , sum(g0.Mix) as Mix
      , sum(g0.Fusers) as Fusers
      , sum(g0.[Full]) as [Full]
      , sum(g0.Mobiel) as Mobiel
      , sum(g0.inkoop) as inkoop
      , cast(sum(g0.omzet) as decimal(8, 2)) as omzet
      , max(g0.doos) as doos
      , max(g0.pallet) as pallet
  from (
      select g.docnumber
          , g.bkjrcode
          , g.periode
          , g.datum
          , a.crdnr
          , case 
              when i.assortment = '130'
                  then g.aantal
              else 0
              end as [Toner Virgin]
          , case 
              when i.assortment = '230'
                  then g.aantal
              else 0
              end as [Inkt Virgin]
          , case 
              when i.assortment in ('120', '220')
                  then g.aantal
              else 0
              end as [Non Virgin]
          , case 
              when i.assortment in ('110', '210')
                  then g.aantal
              else 0
              end as Mix
          , case 
              when i.assortment = '140'
                  then g.aantal
              else 0
              end as Fusers
          , case 
              when i.assortment in ('100', '200')
                  then g.aantal
              else 0
              end as [Full]
          , case 
              when i.assortment between '300'
                      and '330'
                  then g.aantal
              else 0
              end as Mobiel
          , case 
              when i.assortment = '900'
                  then g.aantal
              else 0
              end as Afval
          , case 
              when i.assortment between '100'
                      and '330'
                  or i.assortment = '900'
                  then g.bdr_hfl
              else 0
              end as inkoop
          , a.doos
          , a.pallet
          , isnull(case 
                  when i.assortment between '100'
                          and '330'
                      or i.assortment = '900'
                      then g.aantal * f.verkoop
                  else 0
                  end, 0) as omzet
      from [110]..gbkmut(nolock) g
      inner join [110]..items(nolock) i
          on i.itemcode = g.artcode
              and i.GLAccountDistribution = g.reknr
      left outer join (
          select f.artcode
              , sum((f.esr_aantal * (f.prijs_n * f.koers - ((f.prijs_n * f.koers / 100) * f.korting)))) as verkoop
          from [110]..frhsrg(nolock) f
          where f.fakdat &gt;= getdate() - 365
              and f.esr_aantal &gt; 0
          group by f.artcode
          ) as f
          on f.artcode = g.artcode
      inner join (
          select a.hid
              , c.crdnr
              , case 
                  when a.FreeTextField_11 = '00DOOS'
                      then isnull(a.FreeNumberField_01, 0)
                  else 0
                  end + case 
                  when a.FreeTextField_12 = '00DOOS'
                      then isnull(a.FreeNumberField_02, 0)
                  else 0
                  end + case 
                  when a.FreeTextField_13 = '00DOOS'
                      then isnull(a.FreeNumberField_03, 0)
                  else 0
                  end + case 
                  when a.FreeTextField_14 = '00DOOS'
                      then isnull(a.FreeNumberField_04, 0)
                  else 0
                  end as doos
              , case 
                  when a.FreeTextField_11 &lt;&gt; '00DOOS'
                      and left(a.FreeTextField_11, 2) = '00'
                      then isnull(a.FreeNumberField_01, 0)
                  else 0
                  end + case 
                  when a.FreeTextField_12 &lt;&gt; '00DOOS'
                      and left(a.FreeTextField_12, 2) = '00'
                      then isnull(a.FreeNumberField_02, 0)
                  else 0
                  end + case 
                  when a.FreeTextField_13 &lt;&gt; '00DOOS'
                      and left(a.FreeTextField_13, 2) = '00'
                      then isnull(a.FreeNumberField_03, 0)
                  else 0
                  end + case 
                  when a.FreeTextField_14 &lt;&gt; '00DOOS'
                      and left(a.FreeTextField_14, 2) = '00'
                      then isnull(a.FreeNumberField_04, 0)
                  else 0
                  end as pallet
          from [synergy]..absences(nolock) a
          inner join [synergy]..cicmpy(nolock) c
              on c.cmp_wwn = a.CustomerID
          where a.type = '1000'
              and a.status &lt;&gt; '2'
              and c.textfield3 = 'AM'
              and (
                  left(a.FreeTextField_11, 2) = '00'
                  or left(a.FreeTextField_12, 2) = '00'
                  or left(a.FreeTextField_13, 2) = '00'
                  or left(a.FreeTextField_14, 2) = '00'
                  )
          ) as a
          on cast(a.hid as varchar(max)) = (
                  select max(g1.docnumber)
                  from [110]..gbkmut(nolock) g1
                  where g.bkstnr = g1.bkstnr
                      and g1.reknr = '   170200'
                  )
      where g.transtype = 'N'
          and g.transsubtype = 'T'
          and ltrim(rtrim(g.dagbknr)) = '72'
      ) as g0
  group by g0.docnumber
      , g0.datum
      , g0.crdnr
      , g0.bkjrcode
      , g0.periode
  </code></pre>
  
  <p>I am using a number of subqueries. The error is triggered from within the subquery that starts with 'left outer join (select f.artcode' and ends on the line containing ') as f on f.artcode=g.artcode'.</p>
  
  <p>Within this subquery the line </p>
  
  <p><code>sum((f.esr_aantal * (f.prijs_n * f.koers - ((f.prijs_n * f.koers / 100) * f.korting)))) as verkoop</code> </p>
  
  <p>is causing the error.</p>
  
  <p>All fields in this specific line within the subquery are float types.</p>
  
  <p>Outside this subquery I am using the field 'f.verkoop'.</p>
  
  <p>I have tried to convert all the fields from float to decimal(10,3) but still get the error.</p>
  
  <p>Does anybody know what is wrong with my code?</p>
  
  <p>Eeko</p>