Link to home
Start Free TrialLog in
Avatar of fahVB
fahVB

asked on

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

line added
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)

Avatar of chapmandew
chapmandew
Flag of United States of America image

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,
Perhaps change your parenthesis around a little....

abs(l.draccr/((l.apr/365)*(365/12)))
Avatar of fahVB
fahVB

ASKER

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)
Avatar of fahVB

ASKER

BrandonGalderisi, same error
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
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)


Avatar of fahVB

ASKER

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.
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?
Avatar of fahVB

ASKER

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
is it possible it is one of your other fields, such as l.balance
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.

Avatar of fahVB

ASKER

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)
Sorry... tack on where apr >0 to each query.
Avatar of fahVB

ASKER

First one
---------------------
116.6639

(1 row(s) affected)


Second
i got 92000 rows, mostly filled with zeros
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
Avatar of fahVB

ASKER

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,
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?
Avatar of fahVB

ASKER

nope,
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fahVB

ASKER

Same thing....
Avatar of fahVB

ASKER

Thank you