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*(3 65/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)
line added
case when l.apr = 0 then 0 else abs(l.draccr/(l.apr/365*(3
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)
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)))
abs(l.draccr/((l.apr/365)*
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)
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)
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)
abs(l.draccr/((l.apr/365)*
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)
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.
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?
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','canc elled')
group by m.acct,m.branch,l.sub,l.am ount,l.apr ,l.latefee ,l.balance ,l.deldate ,l.status, l.code,l.t erm,l.open ed,
l.ratechange,l.draccr,h.dr int
order by 2
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)
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','canc
group by m.acct,m.branch,l.sub,l.am
l.ratechange,l.draccr,h.dr
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.
Can you run the following.
select max(draccr/apr) from loan
and this
select abs(cast(((l.draccr/l.apr)
from loan
I'm curious if something with the CASE is balling it up.
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)
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.
ASKER
First one
---------------------
116.6639
(1 row(s) affected)
Second
i got 92000 rows, mostly filled with zeros
---------------------
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
What about the max of the abs. Since you are doing an abs after all
select max(abs(draccr/apr)) from loan
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,
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)
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?
ASKER
nope,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Same thing....
ASKER
Thank you