Solved

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

Posted on 2004-10-19
33
7,679 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:ColumA
  • 21
  • 8
  • 3
  • +1
33 Comments
 
LVL 6

Accepted Solution

by:
JaffaKREE earned 250 total points
Comment Utility
0
 

Author Comment

by:ColumA
Comment Utility
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?!
0
 

Author Comment

by:ColumA
Comment Utility
I read the article, but where do you think the convert has to take place? Is it because real * real ?
0
 
LVL 6

Expert Comment

by:JaffaKREE
Comment Utility
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.
0
 

Author Comment

by:ColumA
Comment Utility
It tells me row30. If I count the empty lines as well, I come to

set @Kurs = @Kurs * @step  

?
0
 
LVL 6

Expert Comment

by:JaffaKREE
Comment Utility
Remove that line, will the procedure run ?
0
 

Author Comment

by:ColumA
Comment Utility
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.
0
 

Author Comment

by:ColumA
Comment Utility
I removed and didn't got the error-msg, but had a time-out msg. Guess because the loop runs forever, not?
0
 

Author Comment

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

Author Comment

by:ColumA
Comment Utility
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?
0
 
LVL 6

Expert Comment

by:JaffaKREE
Comment Utility
This gives you the same error ?
0
 

Author Comment

by:ColumA
Comment Utility
yes, correct. I changed @kurs into real and that got the overflow-error saying on expression into datatype real?!
0
 
LVL 6

Expert Comment

by:JaffaKREE
Comment Utility
can you put in a SELECT @KURS
  and SELECT @STEP
inside the loop ?

is it possible these values are getting extremely high ?

0
 

Author Comment

by:ColumA
Comment Utility
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 ??
0
 

Author Comment

by:ColumA
Comment Utility
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?
0
 
LVL 6

Expert Comment

by:JaffaKREE
Comment Utility
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.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ColumA
Comment Utility
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?
0
 

Author Comment

by:ColumA
Comment Utility
even with step 4 I get the error.

Has this something to do with the code-part ' returns decimal(15,5) as' ?
0
 
LVL 6

Expert Comment

by:JaffaKREE
Comment Utility
what is the exact error message ?
0
 

Author Comment

by:ColumA
Comment Utility
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.
0
 

Author Comment

by:ColumA
Comment Utility
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
0
 

Author Comment

by:ColumA
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:JaffaKREE
Comment Utility
Have you tried using the float datatype ?

0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
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.
0
 

Author Comment

by:ColumA
Comment Utility
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.
0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
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.
0
 

Author Comment

by:ColumA
Comment Utility
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
0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
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.
0
 

Author Comment

by:ColumA
Comment Utility
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?
0
 
LVL 1

Assisted Solution

by:xabimond
xabimond earned 250 total points
Comment Utility
Just to pick up on kselvia's solution in the context of your code I believe you should have:

  if @F1 < @F1_B
  begin
  set @step = 1.01
  while @F1 < @F1_B
  BEGIN
     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
  end


You're using the BEGIN and END correctly to specify the block that you want to run conditionally after your IF statement.  
You just need to go the same after WHILE.
0
 

Author Comment

by:ColumA
Comment Utility
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
0
 

Author Comment

by:ColumA
Comment Utility
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?
0
 

Author Comment

by:ColumA
Comment Utility
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
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

16 Experts available now in Live!

Get 1:1 Help Now