Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
COURSE of the MONTH
7 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Arithmetic overflow-error on converting from numeric into datatype numeric?
Posted on 2004-10-19
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
@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_
WHEN @Kurs> 0 AND @P1_1_pAW_1 < 0 AND @P1_1_pAW_2 > 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_
WHEN @Kurs> 0 AND @P1_1_pAW_1> 0 AND @P1_1_pAW_2 < 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_
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
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.
dbo.GFHilfstabelleV1.P1_1_
dbo.GFBerechnungsbasis.GF1
dbo.GFBerechnungsbasis.GF1
dbo.GFBerechnungsbasis.GFP
FROM dbo.GFHilfstabelleV1 CROSS JOIN
dbo.GFBerechnungsbasis
WHERE (dbo.GFBerechnungsbasis.Be
ALTER function dbo.KurszielSimulator
(@Kurs decimal(15,5),@Kurs_Bilanz
@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_
WHEN @Kurs> 0 AND @P1_1_pAW_1 < 0 AND @P1_1_pAW_2 > 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_
WHEN @Kurs> 0 AND @P1_1_pAW_1> 0 AND @P1_1_pAW_2 < 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_
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
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.
dbo.GFHilfstabelleV1.P1_1_
dbo.GFBerechnungsbasis.GF1
dbo.GFBerechnungsbasis.GF1
dbo.GFBerechnungsbasis.GFP
FROM dbo.GFHilfstabelleV1 CROSS JOIN
dbo.GFBerechnungsbasis
WHERE (dbo.GFBerechnungsbasis.Be
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
21
8-
3
- +1
33 Comments
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?!
I read the article, but where do you think the convert has to take place? Is it because real * real ?
That bug doesn't apply to SQL2000, only 6.5.
This worked fine for me -
DECLARE @One Decimal(15,5)
Set @one = 5.012381237123712371231231
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.
This worked fine for me -
DECLARE @One Decimal(15,5)
Set @one = 5.012381237123712371231231
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.
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.
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.
I removed and didn't got the error-msg, but had a time-out msg. Guess because the loop runs forever, not?
But if I run this line alone by removing the 'while' I do not get an error and the procedure goes well through?
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?
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?
yes, correct. I changed @kurs into real and that got the overflow-error saying on expression into datatype real?!
can you put in a SELECT @KURS
and SELECT @STEP
inside the loop ?
is it possible these values are getting extremely high ?
and SELECT @STEP
inside the loop ?
is it possible these values are getting extremely high ?
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 ??
but have a timeout by executing the function. Even when I set ROWCOUNT 5 ??
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?
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?
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.
Change @step to 1.15 and see what happens.
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?
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?
even with step 4 I get the error.
Has this something to do with the code-part ' returns decimal(15,5) as' ?
Has this something to do with the code-part ' returns decimal(15,5) as' ?
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.
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.
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
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
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.
of the last recordset which does not solve the problem.
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.
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.
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_
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
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.
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
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.
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.
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.
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
Thx
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.
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.
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?
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_
WHEN @Kurs> 0 AND @P1_1_pAW_1 < 0 AND @P1_1_pAW_2 > 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_
WHEN @Kurs> 0 AND @P1_1_pAW_1> 0 AND @P1_1_pAW_2 < 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_
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
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.
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_
WHEN @Kurs> 0 AND @P1_1_pAW_1 < 0 AND @P1_1_pAW_2 > 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_
WHEN @Kurs> 0 AND @P1_1_pAW_1> 0 AND @P1_1_pAW_2 < 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_
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
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.
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
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
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?
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?
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.Bere
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
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.Bere
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
Featured Post
At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Course of the Month7 days, 21 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.