Solved

# formula within select stmt

Posted on 2008-09-30
236 Views
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
Question by:fahVB
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 10
• 8
• 3

LVL 60

Expert Comment

ID: 22608040
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

LVL 39

Expert Comment

ID: 22608047
Perhaps change your parenthesis around a little....

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

Author Comment

ID: 22608053
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 Comment

ID: 22608070
BrandonGalderisi, same error
0

LVL 60

Expert Comment

ID: 22608080
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

LVL 39

Expert Comment

ID: 22608097
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 Comment

ID: 22608101
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

LVL 39

Expert Comment

ID: 22608114
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 Comment

ID: 22608147
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

LVL 60

Expert Comment

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

LVL 39

Expert Comment

ID: 22608206
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 Comment

ID: 22608274
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

LVL 39

Expert Comment

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

Author Comment

ID: 22608863
First one
---------------------
116.6639

(1 row(s) affected)

Second
i got 92000 rows, mostly filled with zeros
0

LVL 39

Expert Comment

ID: 22608973
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 Comment

ID: 22609012
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

LVL 39

Expert Comment

ID: 22609081
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 Comment

ID: 22609115
nope,
0

LVL 39

Accepted Solution

BrandonGalderisi earned 500 total points
ID: 22609254
Why the group by?  You have no aggregates.  That may be causing troubles.
0

Author Comment

ID: 22609409
Same thing....
0

Author Closing Comment

ID: 31501657
Thank you
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What â€¦
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wiâ€¦
Nobody understands Phishing better than an anti-spam company. Thatâ€™s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With companâ€¦
Six Sigma Control Plans
###### Suggested Courses
Course of the Month7 days, 3 hours left to enroll