Solved

formula within select stmt

Posted on 2008-09-30
21
236 Views
Last Modified: 2010-04-21
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)

0
Comment
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
  • Learn & ask questions
  • 10
  • 8
  • 3
21 Comments
 
LVL 60

Expert Comment

by:chapmandew
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

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

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

Author Comment

by:fahVB
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:fahVB
ID: 22608070
BrandonGalderisi, same error
0
 
LVL 60

Expert Comment

by:chapmandew
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

by:BrandonGalderisi
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

by:fahVB
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

by:BrandonGalderisi
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

by:fahVB
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

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

Expert Comment

by:BrandonGalderisi
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

by:fahVB
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

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

Author Comment

by:fahVB
ID: 22608863
First one
---------------------
116.6639

(1 row(s) affected)


Second
i got 92000 rows, mostly filled with zeros
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
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

by:fahVB
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

by:BrandonGalderisi
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

by:fahVB
ID: 22609115
nope,
0
 
LVL 39

Accepted Solution

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

Author Comment

by:fahVB
ID: 22609409
Same thing....
0
 

Author Closing Comment

by:fahVB
ID: 31501657
Thank you
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

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…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question