Link to home
Start Free TrialLog in
Avatar of ColumA
ColumA

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of JaffaKREE
JaffaKREE
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ColumA
ColumA

ASKER

I use SQL2000. When I immunize ' while @F1 < @F1_B' then I don't get an error, but it might be because it has not go for the loop?!
Avatar of ColumA
ColumA

ASKER

I read the article, but where do you think the convert has to take place? Is it because real * real ?
Avatar of JaffaKREE
JaffaKREE
Flag of United States of America image
That bug doesn't apply to SQL2000, only 6.5.

This worked fine for me -

DECLARE @One Decimal(15,5)
Set @one = 5.012381237123712371231231231231221421
DECLARE @two decimal(15,5)
Set @TWO = 1.51231231237120312312

set @one = @one * @two
select @one

So I'm not sure that line is causing the error.  Can you remove most of the code (the set statements) to narrow it down ?
 if you run this through query analyzer, you can double click on the error message and it will take you to the offending line.
Avatar of ColumA
ColumA

ASKER

It tells me row30. If I count the empty lines as well, I come to

set @Kurs = @Kurs * @step  

?
Avatar of JaffaKREE
JaffaKREE
Flag of United States of America image
Remove that line, will the procedure run ?
Avatar of ColumA
ColumA

ASKER

Executing your code results in 7.58027. Works. ?

Can it be because I have some values like '0.003' in the @Kurs. If I multiply by 100, I get 0.00303 ? to much decimals? I don't think so if I see the result of 7.58027.
Avatar of ColumA
ColumA

ASKER

I removed and didn't got the error-msg, but had a time-out msg. Guess because the loop runs forever, not?
Avatar of ColumA
ColumA

ASKER

But if I run this line alone by removing the 'while' I do not get an error and the procedure goes well through?
Avatar of ColumA
ColumA

ASKER

I removed all and only run

if @F1 < @F1_B
  begin
  set @step = 1.01
  while @F1 < @F1_B
  set @Kurs = @Kurs * @step  
end

but does not work. So I guess we have the confirmation that it not lies in the 'set'-instructions, or?
Avatar of JaffaKREE
JaffaKREE
Flag of United States of America image
This gives you the same error ?
Avatar of ColumA
ColumA

ASKER

yes, correct. I changed @kurs into real and that got the overflow-error saying on expression into datatype real?!
Avatar of JaffaKREE
JaffaKREE
Flag of United States of America image
can you put in a SELECT @KURS
  and SELECT @STEP
inside the loop ?

is it possible these values are getting extremely high ?

Avatar of ColumA
ColumA

ASKER

yes, I think so. I have to go for 12'000 recordsets. But now, I changed @Kurs into 'int' and I don't get the error anymore,
but have a timeout by executing the function. Even when I set ROWCOUNT 5 ??
Avatar of ColumA
ColumA

ASKER

I changed to

if @Kurs < 10000
  begin
  set @step = 1.01
  while @Kurs < 10000
  set @Kurs = @Kurs * @step

which should be easy to go for but still has a timeout with ROWCOUNT 5 ? Do you think this is because the 'int' definition?
Avatar of JaffaKREE
JaffaKREE
Flag of United States of America image
I don't think Rowcount is affecting this statement, because the rows are not being outputted.

Change @step to 1.15 and see what happens.

Avatar of ColumA
ColumA

ASKER

with 1.15, I get the overflow-error again saying problems expression into datatype int ?

If I run


 if @F1 < 100
  begin
  set @step = 1.01
  while @F1 < 100
  set @F1 = @F1 * @step

it goes through very quickly which looks like the procedure to execute the function should be ok?
Avatar of ColumA
ColumA

ASKER

even with step 4 I get the error.

Has this something to do with the code-part ' returns decimal(15,5) as' ?
Avatar of JaffaKREE
JaffaKREE
Flag of United States of America image
what is the exact error message ?
Avatar of ColumA
ColumA

ASKER

arithmetic overflow-error on converting from expression into datatype int   'translated from German

All first 5 recordsets should apply for the loop. I can't figure out why it should hang. They all have values coming on @Kurs, @F1. I just tried with the return of @F1,
with removing the loop, and it gave back the right figures and very quick.
Avatar of ColumA
ColumA

ASKER

I went for a raw-code like

ALTER function dbo.KurszielSimulator1
(@Kurs decimal(15,5),@F1_B decimal(15,5),@F1 decimal (15,5),@GFP_1 tinyint) returns decimal(15,5) as
begin
     declare @step decimal(15,5)
     if @F1 < @F1_B
     begin
          set @step = 1.001
          while @F1 < @F1_B
               set @F1 = @F1 * @step
      set @Kurs = @Kurs * @step
     end
      return @Kurs
end

which worked but when I check, it didn't count up @Kurs

but going for

  begin
          set @step = 1.001
          while @F1 * @step < @F1_B
         set @Kurs = @Kurs * @step

I get the overflow again
Avatar of ColumA
ColumA

ASKER

It seems that it has to do something with the size. What would be another way to go for such a simulation? I thought for a stored procedure, but this returns only the value
of the last recordset which does not solve the problem.
Avatar of JaffaKREE
JaffaKREE
Flag of United States of America image
Have you tried using the float datatype ?

Avatar of Ken Selvia
Ken Selvia
Flag of United States of America image
I browsed through most of the messages here and it still looks to me like the problem is this:

while @F1 < @F1_B
  set @Kurs = @Kurs * @step  

That will never exit. (Or at least not until @Krus causes arithmetic overflow)

How are you expecting the values of @F1 and @F1_B to change without changing them in the loop?  With no BEGIN/END only the next statement following the WHILE is done until it overflows.
Avatar of ColumA
ColumA

ASKER

Hi, @F1_B will stay and not be changed. @F1 should increase automaticaly, because as higher as @kurs goes, @P1_1  '> 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_pAW_2'
should change in value and therefore influence @F1_1, where a higher ratio results in a new catagory ' 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' and will have more points 'GFP_3'. At the end of this loop, @F1 will be newly calculated '  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 ' with a higher quote as @kurs moves higher. Once it should hit @F1_B. I tried each parameter by its own
without the loop and I get back the correct values.

I think as well that the bug has to be somewhere there. Or can you imagine that SQL is not able to loop for 12'000 recordsets? If this is fixed, do I get anyway a timeout. Do I have
to go for another way and if yes, what can this look like. I am happy to grant more points because this issue is important for me.
Thx.
Avatar of Ken Selvia
Ken Selvia
Flag of United States of America image
I semi-understand all that, but the basic problem remains;

This statement alone

while @F1 < @F1_B
  set @Kurs = @Kurs * @step

will never exit. Ever. (until it crashes)  There is no BEGIN after the WHILE so the WHILE applies only to the SET statement which in itself, will never produce a condition that makes the while condition false and exit.
Avatar of ColumA
ColumA

ASKER

As I am new to SQL, I had found a WHILE statement and thought that I can expand. Can you pls help me and post the code-part where the BEGIN END has to be placed.
Thx
Avatar of Ken Selvia
Ken Selvia
Flag of United States of America image
It is late and I'm about to sleep, but it looks like you were on the right track, you just need to put BEGIN END directives after the WHILE.

WHILE {condition}
BEGIN

  statements

END

Similar to an IF statement

IF {Condition}
BEGIN

END

Without the BEGIN END, the statement applies only to the very next statement.

WHILE {condition}
   STATEMENT

will execute STATEMENT in an infinate loop if the statement does not do something to make {condition} false.
Avatar of ColumA
ColumA

ASKER

ok, let me try to implement and I'll give you a feedback. Anyway thx for the moment and have a good sleep. I imagine you stay in Asia, right?
SOLUTION
Avatar of xabimond
xabimond
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ColumA
ColumA

ASKER

Thx a lot for posting the code. I now get a syntax-error saying an error near return, row 36 (?? is far away from return), wrong syntax near 'end'. But I have 3 BEGIN & 3 END.
What is wrong here

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
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
Avatar of ColumA
ColumA

ASKER

Found the 'end' problem. But if I go for @step = 1.001, I get a timeout but if I go for 1.004, it works but overshoots on @kurs, meaning it goes one step to far.
As I understand, it should stop one notch below reaching @F1_B. Might have this something to do with the bigger steps and if yes, is there a way
to avoid timeout or is more IT-Power needed?
Avatar of ColumA
ColumA

ASKER

The other problem appearing, as it overshoots, I can't go for the 'else if' instruction on simulating downwards. So I made a second function/procedure for the down-calculation.
But as it sets the non-simulated prices as @kurs, it overwrites me the previous upward-simulted prices. I tried to set the Filter in the procedure like

WHERE     (dbo.GFHilfstabelleV1.Berechnungsvariante = '1') AND (dbo.GFHilfstabelleV1.F1 > 0) AND (dbo.GFHilfstabelleV1.F1_B > 0) AND (dbo.GFHilfstabelleV1.F1 > dbo.GFHilfstabelleV1.F1_B)

but it hangs on the first recordset where F1 < F1_B ?? On the other procedure with the vice versa instruction, I have no problems appearing?
Any idea? I know that this goes bit more far than my initial question was, but as you got close to my problem, I would like to mention it here.
If you agree, I will after grant kselvia 500points in a special Q as he gave the input which brought us to clear the issue and split up the 500points from this Q between JaffaKREE and xabimond.

Thx
ColumA