May 10th, 2004, 08:28 AM
|
 |
Contributing User
|
|
Join Date: Feb 2004
Location: Belgium, Antwerp
Posts: 177
Time spent in forums: 24 sec
Reputation Power: 5
|
|
Calculation errors or MS bug in recursive functions ??
are there know problems with calculation errors in recursive function in sql server 2000 ?
if not, please point out the error in the following:
PHP Code:
CREATE FUNCTION dbo.func_RecVerkoopprijs_basis (@Parent bigint, @Versie_ID bigint)
RETURNS decimal(10,2) AS
BEGIN
DECLARE @Value decimal(10,2)
IF (@Parent IS null) OR (@Parent IN (SELECT Parent_ID FROM Offerte_Lijnen WHERE Versie_ID = @Versie_ID))
BEGIN
IF (@Parent IS null)
BEGIN
SET @Value = (SELECT SUM(dbo.func_RecVerkoopprijs_basis(Lijn_ID,Versie_ ID) * (1-Discount)) FROM view_Full_Lijnen WHERE Parent_ID IS null AND Versie_ID = @Versie_ID)
END
ELSE
BEGIN
SET @Value = (SELECT SUM(dbo.func_RecVerkoopprijs_basis(Lijn_ID,Versie_ ID) * (1-Discount)) FROM view_Full_Lijnen WHERE Parent_ID = @Parent)
END
END
ELSE
BEGIN
SET @Value = (SELECT (Aantal * Actuele_Verkoopprijs) as prijs FROM view_Full_Lijnen WHERE Lijn_ID = @Parent and optioneel = 0)
END
RETURN @Value
END
select dbo.func_recverkoopprijs_basis(25,5) as p
this returns 4193 (wrong because this is supose to be 4220)
Reason why:
select Actuele_Verkoopprijs * Aantal
from view_Full_Lijnen
where Parent_ID=25 AND Versie_ID=5
returns:
400.0000
270.0000
250.0000
3300.0000
THEN:
select sum(Actuele_Verkoopprijs * Aantal)
from view_Full_Lijnen
where Parent_ID=25 AND Versie_ID=5
returns: 4220 (this is right)
EVEN:
select sum(Actuele_Verkoopprijs * Aantal)*(1-(select discount from view_Full_Lijnen where lijn_ID=25 AND Versie_ID=5))
from view_Full_Lijnen
where Parent_ID=25 AND Versie_ID=5
returns: 4220 (still right)
And yet the salst statement should do the exact same thing the function does
 anyone any clue
|
ASP Free Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
