Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 12th, 2005, 01:32 PM
Authorized User
 
Join Date: Dec 2004
Location: Las Vegas, NV, USA.
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default arithmetic overflow problem

Does anyone can help me to solve this arithmetic overflow problem? Thanks!

declare @details table (
                star_issuer_id int, star_ratetype_id int,
                 Negative int, Negative_Amt money,
                Accepted int, Accepted_Amt money,
                Rejected int, Rejected_amt money,
                NOBill_Cnt int, NoBill_Amt money,
                Accept_Expense money default 0.00,
                Reject_Expense money default 0.00
insert into @details (
                star_issuer_id, star_ratetype_id,
                Negative, Negative_Amt,
                Accepted, Accepted_Amt,
                Rejected, Rejected_amt,
                NOBill_Cnt, NoBill_Amt
                )

        select s.star_issuer_id,
            t2.star_ratetype_id,

            sum(case when s.star_code_id=0 and Face < 0 then 1 when s.star_code_id>0 and Face < 0 then 0 else 0 end ) as Negative,
            sum(case when s.star_code_id=0 and Face < 0 then cast(sir.auth_rate_base as money) when s.star_code_id>0 and Face < 0 then -(cast(sir.auth_rate_base as money)) else 0.00 end ) as Negative_Amt,

            sum(case when s.star_code_id=0 and face >= 0 then 1 else 0 end ) as Accepted,
            sum(case when s.star_code_id=0 and face >= 0 then cast(sir.auth_rate_base as money) else 0.00 end ) as Accepted_Amt,

            sum(case when s.star_code_id=0 and face >= 0 then 0 else 1 end ) as Rejected,
            sum(case when s.star_code_id=0 and face >= 0 then 0.00 else cast(sir.decline_rate_base as money) end ) as Rejected_Amt,

            sum(case when s.star_code_id=0 and face >= 0 then 0 when nb.star_issuer_id is null then 0 else 1 end ) as NoBill_Cnt,
            sum(case when s.star_code_id=0 and face >= 0 then 0.00 when nb.star_issuer_id is null then 0.00 else cast(sir.decline_rate_base as money) end ) as NoBill_Amt

                )
        from trn_dtl_star S with (Nolock)

            inner join val_star_type2 t2 with (nolock)
                on t2.star_type2_id = s.star_type2_id

            left join val_star_issuer_nobill nb with (nolock)
                on nb.star_issuer_id = s.star_issuer_id
                and nb.star_code_id = s.star_code_id

            left join (
                    select sir.*
                    from val_star_issuer_rate sir with (nolock)
                    inner join @rates r
                        on r.star_issuer_id = sir.star_issuer_id
                        and r.star_ratetype_id = sir.star_ratetype_id
                    ) sir
                on s.star_issuer_id = sir.star_issuer_id
                and t2.star_ratetype_id = sir.star_ratetype_id
            where s.settle_date between @dFrom and @dThru
                and s.report_id between 43 and 50
            group by s.star_issuer_id, t2.star_ratetype_id

Note: the data type of "auth_rate_base" in the sir (star_issuer_id) table: smallmoney.


    When I executed this code and received the following error msg:
Server: Msg 220, Level 16, State 3, Procedure ACCT_Report_RevInt_Sum, Line 267
Arithmetic overflow error for data type smallmoney, value = 296524.
The statement has been terminated.



flyfish
__________________
flyfish
Reply With Quote
  #2 (permalink)  
Old July 12th, 2005, 01:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The smallmoney data type will overflow on values outside the range [-214748.3648 .. 214748.3647]. These are basically integers / 10000. Try changing to the money data type.

Rand
Reply With Quote
  #3 (permalink)  
Old July 12th, 2005, 02:29 PM
Authorized User
 
Join Date: Dec 2004
Location: Las Vegas, NV, USA.
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Can I cast smallmoney to money instead of changing data type in the table?

flyfish
Reply With Quote
  #4 (permalink)  
Old July 12th, 2005, 02:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think you can, as long as you are not actually writing to the table. Why are you using smallmoney in the table?


Rand
Reply With Quote
  #5 (permalink)  
Old July 12th, 2005, 04:37 PM
Authorized User
 
Join Date: Dec 2004
Location: Las Vegas, NV, USA.
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This table is not created by me. My question is why I still got error msg while I already used cast in the SQL statement as follow:

sum(case when s.star_code_id=0 and Face < 0 then cast(sir.auth_rate_base as money) when s.star_code_id>0 and Face < 0 then -(cast(sir.auth_rate_base as money)) else 0.00 end ) as Negative_Amt,


flyfish
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
simple arithmetic overflow vbscript handy Classic ASP Basics 32 March 31st, 2009 04:25 PM
Arithmetic overflow error gregalb Reporting Services 1 June 24th, 2008 02:42 PM
Odd arithmetic overflow error.... 7racer SQL Server 2000 4 May 12th, 2006 09:17 AM
Arithmetic overflow error converting expression to sinapra Classic ASP Databases 8 September 3rd, 2004 12:24 AM
Arithmetic overflow error converting expression to spikey SQL Server 2000 1 June 24th, 2003 07:19 PM



All times are GMT -4. The time now is 11:38 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.