Solved

formula within select stmt

Posted on 2008-09-30
21
232 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
  • 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
 

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
count vs exists 9 60
Pivot Query Problem 9 37
Set the max value for a column 7 37
MS SQLK Server multi-part identifier cannot be bound 5 25
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

919 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now