• Status: Solved
• Priority: Medium
• Security: Public
• Views: 253

# formula within select stmt

i have added this line in my select stmt and it is cmoing back with the followign error, i am think that i have to round decimal to two digits in order to fix and need help with that please

case when l.apr = 0 then 0 else abs(l.draccr/(l.apr/365*(365/12))) end as AAVGBALA,

Error

Msg 248, Level 16, State 1, Line 8
The conversion of the varchar value '100003004075' overflowed an int column. Maximum integer value exceeded.

(0 row(s) affected)

0
fahVB
• 10
• 8
• 3
1 Solution

Commented:
case when l.apr = 0 then 0 else abs(cast(l.draccr as bigint)/(cast(l.apr as bigint)/365*(365/12))) end as AAVGBALA,
0

Commented:
Perhaps change your parenthesis around a little....

abs(l.draccr/((l.apr/365)*(365/12)))
0

Author Commented:
coming back with same error
Msg 248, Level 16, State 1, Line 8
The conversion of the varchar value '100003004075' overflowed an int column. Maximum integer value exceeded.

(0 row(s) affected)
0

Author Commented:
BrandonGalderisi, same error
0

Commented:
case when cast(l.apr as bigint) = cast(0 as bigint) then 0 else abs(cast(l.draccr as bigint)/(cast(l.apr as bigint)/365*(365/12))) end as AAVGBALA
0

Commented:
100003004075 is a valid bigint.

abs(l.draccr/((l.apr/365)*(365/12)))

365/12 is a static value (30).  So iet's just use that and take advantage of the order of operations :)

abs(cast(((l.draccr/l.apr)/365) as bigint)*30)

0

Author Commented:
same error
Msg 248, Level 16, State 1, Line 8
The conversion of the varchar value '100003004075' overflowed an int column. Maximum integer value exceeded.
0

Commented:
Let's see the whole query.  Are you by chance using this in a union where the first part of the union is an INT, or selecting it into a temp table (@ or #) where the data type is defined as an int?
0

Author Commented:
Here is my full query...

Select m.branch as AORGID,l.code as ALNTYPE,abs(h.drint) as AINTINC,
case when l.apr = 0 then 0 else abs(cast(((l.draccr/l.apr)/365) as bigint)*30) end as AAVGBALA,
abs(l.balance) as AACTBALA,l.amount as AORIGAMT,l.apr as AINTRATE
from memb m
INNER JOIN loan l on m.rowno  = l.rowno_membloan_memb
INNER JOIN lohi h on l.rowno = h.rowno_loanlohi_loan
where l.status not in ('closed','writeoff','cancelled')
group by m.acct,m.branch,l.sub,l.amount,l.apr,l.latefee,l.balance,l.deldate,l.status,l.code,l.term,l.opened,
l.ratechange,l.draccr,h.drint
order by 2
0

Commented:
is it possible it is one of your other fields, such as l.balance
0

Commented:
So you aren't doing any type of inserts on this.

Can you run the following.

select max(draccr/apr) from loan

and this

select abs(cast(((l.draccr/l.apr)/365) as bigint)*30)
from loan

I'm curious if something with the CASE is balling it up.

0

Author Commented:
first one comes back immediatly with this error
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

(0 row(s) affected)

second one runs but it ends up in this error
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

(309 row(s) affected)
0

Commented:
Sorry... tack on where apr >0 to each query.
0

Author Commented:
First one
---------------------
116.6639

(1 row(s) affected)

Second
i got 92000 rows, mostly filled with zeros
0

Commented:
Ok.  Well now we know that you have a numeric data type.

What about the max of the abs.  Since you are doing an abs after all

select max(abs(draccr/apr)) from loan
0

Author Commented:
got this 4813.3269

you know i ran it again with this line and this time i didn't get any errors, but again most of the data is zero

case when l.apr = 0 then 0 else abs(cast(((l.draccr/l.apr)/365) as bigint)*30) end as AAVGBALA,
0

Commented:
And you aren't attempting to select this through a view with a defined type, with a union, into a table, or anything like that?
0

Author Commented:
nope,
0

Commented:
Why the group by?  You have no aggregates.  That may be causing troubles.
0

Author Commented:
Same thing....
0

Author Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.