Do not use on any
shared computer
August 29, 2008 07:05pm pdt
 
[x]
Attachment Details

Arithmetic overflow-error on converting from numeric into datatype numeric?

Tags: overflow, arithmetic, error, converting
I am creating following function, while executing it I get the error-msg in my title. Any idea how to fix? I got trough step by step and it seems that it lies in ' set @Kurs = @Kurs * @step'


ALTER function dbo.KurszielSimulator
(@Kurs decimal(15,5),@Kurs_Bilanz_FX decimal(15,5),@F1_B real,@F1 real,
@P1_1_pAW_1 real,@P1_1_pAW_2 real,@P1_2_pAW real ,@P1_3_pAW real,@P1_4_pAW real,@P1_5_pAW real,@P1_6_pAW real,@P1_7_pAW real,@P1_8_pAW real,@P1_9_pAW real,@P1_10_pAW real,@P1_11_pAW real,@P1_12_pAW real,@P1_13_pAW real,@P1_14_pAW real,@P1_15_pAW real,
@GF1_1_1mi real, @GF1_1_1ma real, .....                    @GF1_15_10mi real, @GF1_15_10ma real,
@GFP_1 tinyint,@GFP_2 tinyint,@GFP_3 tinyint,@GFP_4 tinyint,@GFP_5 tinyint,@GFP_6 tinyint,@GFP_7 tinyint,@GFP_8 tinyint,@GFP_9 tinyint,@GFP_10 tinyint) returns decimal(15,5) as

begin

declare @step decimal(15,5),@F1prov real,@P1_1 real,@F1_1 tinyint,@P1_2 real,@F1_2 tinyint,@P1_3 real,@F1_3 tinyint,@P1_4 real,@F1_4 tinyint,@P1_5 real,@F1_5 tinyint,@P1_6 real,@F1_6 tinyint,@P1_7 real,@F1_7 tinyint,@P1_8 real,@F1_8 tinyint,@P1_9 real,@F1_9 tinyint,@P1_10 real,@F1_10 tinyint,@P1_11 real,@F1_11 tinyint,@P1_12 real,@F1_12 tinyint,@P1_13 real,@F1_13 tinyint,@P1_14 real,@F1_14 tinyint,@P1_15 real,@F1_15 tinyint

-- Berechnung Kursziel nach oben
  if @F1 < @F1_B
  begin
  set @step = 1.01
  while @F1 < @F1_B
  set @Kurs = @Kurs * @step  

      set @P1_1 = (CASE WHEN @Kurs> 0 AND @P1_1_pAW_1 > 0 AND @P1_1_pAW_2 > 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_pAW_2
                        WHEN @Kurs> 0 AND @P1_1_pAW_1 < 0 AND @P1_1_pAW_2 > 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_pAW_2
                        WHEN @Kurs> 0 AND @P1_1_pAW_1> 0 AND @P1_1_pAW_2  < 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_pAW_2
                        WHEN @Kurs> 0 AND @P1_1_pAW_1 < 0 AND @P1_1_pAW_2 < 0 THEN (@Kurs/@P1_1_pAW_1 )/ABS(@P1_1_pAW_2) ELSE NULL END)
      
      set @F1_1 = (CASE WHEN @P1_1 >= @GF1_1_1mi AND @P1_1 < @GF1_1_1ma THEN @GFP_1
                        WHEN @P1_1 >= @GF1_1_2mi AND @P1_1 < @GF1_1_2ma THEN @GFP_2
                        WHEN @P1_1 >= @GF1_1_3mi AND @P1_1 < @GF1_1_3ma THEN @GFP_3
                        WHEN @P1_1 >= @GF1_1_4mi AND @P1_1 < @GF1_1_4ma THEN @GFP_4
                        WHEN @P1_1 >= @GF1_1_5mi AND @P1_1 < @GF1_1_5ma THEN @GFP_5
                        WHEN @P1_1 >= @GF1_1_6mi AND @P1_1 < @GF1_1_6ma THEN @GFP_6
                        WHEN @P1_1 >= @GF1_1_7mi AND @P1_1 < @GF1_1_7ma THEN @GFP_7
                        WHEN @P1_1 >= @GF1_1_8mi AND @P1_1 < @GF1_1_8ma THEN @GFP_8
                        WHEN @P1_1 >= @GF1_1_9mi AND @P1_1 < @GF1_1_9ma THEN @GFP_9
                  --ACHTUNG: BEI NACHFOLGENDEM PARAMETER WURDE 'AND' MIT 'OR' ERSETZT
                        WHEN @P1_1 >= @GF1_1_10mi OR @P1_1 < @GF1_1_10ma THEN @GFP_10
                        ELSE NULL END)

      set @P1_2 = (CASE WHEN @P1_2_pAW <> 0 THEN @Kurs * @Kurs_Bilanz_FX / @P1_2_pAW ELSE NULL END)

      set @F1_2 =  (CASE WHEN @P1_2 >= @GF1_2_1mi AND @P1_2 < @GF1_2_1ma THEN @GFP_1
                              WHEN @P1_2 >= @GF1_2_2mi AND @P1_2 < @GF1_2_2ma THEN @GFP_2
                              WHEN @P1_2 >= @GF1_2_3mi AND @P1_2 < @GF1_2_3ma THEN @GFP_3
                              WHEN @P1_2 >= @GF1_2_4mi AND @P1_2 < @GF1_2_4ma THEN @GFP_4
                              WHEN @P1_2 >= @GF1_2_5mi AND @P1_2 < @GF1_2_5ma THEN @GFP_5
                              WHEN @P1_2 >= @GF1_2_6mi AND @P1_2 < @GF1_2_6ma THEN @GFP_6
                              WHEN @P1_2 >= @GF1_2_7mi AND @P1_2 < @GF1_2_7ma THEN @GFP_7
                              WHEN @P1_2 >= @GF1_2_8mi AND @P1_2 < @GF1_2_8ma THEN @GFP_8
                              WHEN @P1_2 >= @GF1_2_9mi AND @P1_2 < @GF1_2_9ma THEN @GFP_9
                  --ACHTUNG: BEI NACHFOLGENDEM PARAMETER WURDE 'AND' MIT 'OR' ERSETZT
                              WHEN @P1_2 >= @GF1_2_10mi OR @P1_2 < @GF1_2_10ma THEN @GFP_10
                              ELSE NULL END)

      SET @F1 = convert(real,(isnull(@F1_1,0) + isnull(@F1_2,0) + isnull(@F1_3,0) + isnull(@F1_4,0) + isnull(@F1_5,0) + isnull(@F1_6,0) + isnull(@F1_7,0) + isnull(@F1_8,0) + isnull(@F1_9,0) + isnull(@F1_10,0) + isnull(@F1_11,0) + isnull(@F1_12,0) + isnull(@F1_13,0) + isnull(@F1_14,0) + isnull(@F1_15,0)))/
                            convert(real, (
                              CASE WHEN @F1_1 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_2 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_3 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_4 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_5 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_6 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_7 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_8 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_9 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_10 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_11 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_12 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_13 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_14 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_15 IS NULL THEN 0 ELSE 1 END +
                              CASE WHEN @F1_1 IS NULL  AND @F1_2 IS NULL AND @F1_3 IS NULL AND @F1_4 IS NULL AND @F1_5 IS NULL AND @F1_6 IS NULL AND @F1_7 IS NULL AND @F1_8 IS NULL AND @F1_9 IS NULL AND @F1_10 IS NULL AND @F1_11 IS NULL AND @F1_12 IS NULL AND @F1_13 IS NULL AND @F1_14 IS NULL AND @F1_15 IS NULL THEN 1 ELSE 0 END))            

   end

-- Berechnung Kursziel nach unten
--     else if @F1 > @F1_B
  --   begin
    --      set @step = 0.99
      --    while @F1 * @step > @F1_B
        --       set @F1 = @F1 * @step
    -- end
     return @Kurs
end

The execution-code looks like:

ALTER PROCEDURE Kurszieltestsim
AS
SET ROWCOUNT 50
UPDATE    dbo.GFHilfstabelleV1
SET              Systemkursziel =       dbo.KurszielSimulator(dbo.GFHilfstabelleV1.Kurs,dbo.GFHilfstabelleV1.Kurs_Bilanz_FX,dbo.GFHilfstabelleV1.F1_B,dbo.GFHilfstabelleV1.F1,
                        dbo.GFHilfstabelleV1.P1_1_pAW_1 , dbo.GFHilfstabelleV1.P1_1_pAW_2,dbo.GFHilfstabelleV1.P1_2_pAW,dbo.GFHilfstabelleV1.P1_3_pAW,dbo.GFHilfstabelleV1.P1_4_pAW,dbo.GFHilfstabelleV1.P1_5_pAW,dbo.GFHilfstabelleV1.P1_6_pAW,dbo.GFHilfstabelleV1.P1_7_pAW,dbo.GFHilfstabelleV1.P1_8_pAW,dbo.GFHilfstabelleV1.P1_9_pAW,dbo.GFHilfstabelleV1.P1_10_pAW,dbo.GFHilfstabelleV1.P1_11_pAW,dbo.GFHilfstabelleV1.P1_12_pAW,dbo.GFHilfstabelleV1.P1_13_pAW,dbo.GFHilfstabelleV1.P1_14_pAW,dbo.GFHilfstabelleV1.P1_15_pAW,
      dbo.GFBerechnungsbasis.GF1_1_1mi, dbo.GFBerechnungsbasis.GF1_1_1ma, dbo.GFBerechnungsbasis.GF1_1_2mi,
                      dbo.GFBerechnungsbasis.GF1_1_2ma, .......  dbo.GFBerechnungsbasis.GF1_15_10ma,
                        
dbo.GFBerechnungsbasis.GFP_1,dbo.GFBerechnungsbasis.GFP_2,dbo.GFBerechnungsbasis.GFP_3,dbo.GFBerechnungsbasis.GFP_4,dbo.GFBerechnungsbasis.GFP_5,dbo.GFBerechnungsbasis.GFP_6,dbo.GFBerechnungsbasis.GFP_7,dbo.GFBerechnungsbasis.GFP_8,dbo.GFBerechnungsbasis.GFP_9,dbo.GFBerechnungsbasis.GFP_10)
                     FROM         dbo.GFHilfstabelleV1 CROSS JOIN
                      dbo.GFBerechnungsbasis
WHERE     (dbo.GFBerechnungsbasis.Berechnungsvariante = 1) AND F1 <> 0
Start your free trial to view this solution
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

Question Stats
Zone: Microsoft
Question Asked By: ColumA
Solution Provided By: JaffaKREE
Participating Experts: 3
Solution Grade: A
Views: 24
Translate:
Loading Advertisement...
 
[+][-]Accepted Solution by JaffaKREE
Accepted Solution by JaffaKREE:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by JaffaKREE
Expert Comment by JaffaKREE:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by JaffaKREE
Expert Comment by JaffaKREE:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by JaffaKREE
Expert Comment by JaffaKREE:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by JaffaKREE
Expert Comment by JaffaKREE:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by JaffaKREE
Expert Comment by JaffaKREE:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by JaffaKREE
Expert Comment by JaffaKREE:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by JaffaKREE
Expert Comment by JaffaKREE:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by kselvia

Rank: Guru

Expert Comment by kselvia:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by kselvia

Rank: Guru

Expert Comment by kselvia:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Expert Comment by kselvia

Rank: Guru

Expert Comment by kselvia:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Assisted Solution by xabimond
Assisted Solution by xabimond:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by ColumA
Author Comment by ColumA:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
20080723-EE-VQP-34