July 16, 2002 at 4:36 am
We are encountering a peculiar problem regarding the usage of aggregate functions in combination with user-defined functions.
When the same UDF with different set of arguments and enclosed within the same aggregate
function is used more than once in a query, it returns the value of the first occurence of the UDF for all subsequent occurences in the query.
For e.g.
The query returns the result as below
select max(dbo.c_ret('99','01/01/02',1)) AS col1 ,
max(dbo.c_ret('99','01/01/01',1)) AS col2
Col1 Col2
2002 2002
Actually it should return
Col1 Col2
2002 2001
TIA
Suresh Kumar.N
July 16, 2002 at 6:04 am
Sorry, need to see the UDF itself to understand possible why.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 19, 2002 at 8:49 am
You are in luck. I thought I was runnign SQL 2K with SP2 and found I
wasn't, thus I got an error. Found that this is a known issue corrected
with SP1 see
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q288957 . I have
tested and works fine now.
Please post this information back to the threads as other may run across
the same issue.
<nsureshkumar@indiainfo.com> on 07/17/2002 09:20:48 AM
To: James Travis/AO/USR/FTU@WACHOVIA
cc:
Subject: Sent From SQLServerCentral.com by pro
Hello Antares686
You received the following message from : pro (nsureshkumar@indiainfo.com)
At: http://www.sqlservercentral.com/forum/
Hi Antares,
The function given below can be taken as an example
CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
Case :1
select dbo.CubicVolume(2,3,4) as col1,
dbo.CubicVolume(2,5,6) as col2
Returns
col1 col2
24 60
The problem arises, when used along with the aggregate functions.
Case :2
select max(dbo.CubicVolume(2,3,4)) as col1,
max(dbo.CubicVolume(2,5,6)) as col2
Returns
col1 col2
24 24
Note that the combination of aggregate function in both the columns and the
value for the first argument (for both) is same.
This is causing the issue. Changing either the aggregate function (with SUM
& MAX combination) or the first argument results in the original value.
HTH
Pro
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply