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

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

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

flyfish

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


Rand

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