A domain error occurred. WTH ?
-
Hello..
I'm trying to call a simple UDF from a cursor and I'm getting and error which is simply "A domain error occurred".
I'm using a cursor only because I get the same error when trying to call the UDF from a correlated subquery - ugh!
I'm totally frustrated as I've used the UDF for months now and had great plans for it's re-use. However, with this new problem the UDF may have to be scrapped.
Anyone else run into this problem ?
PLEASE help!
-
Can you post the code
Guess some funny/invalid value somewhere.
Example:
Select SQRT(-10)
-
OK -U asked for it...
Further testing reveals that the UDF works fine as standalone(and has for months) but called from a cursor fails. If I remove the @PortfolioPct in the main calculation - it works beleive it or not - gasp!
CREATE FUNCTION peGetFundFactoredPerformance
(
@id_MutualFund int,
@FactorNum udtPerformanceFactor,
@PortfolioPct udtPerformanceFactor, -- Really an INT but cast to decimal for precsion purposes
@BeginDate smalldatetime,
@EndDate smalldatetime
)
RETURNS udtPerformanceReturn
AS
/*
Calculate Cumulative performance of any given index for a date range
Use NOLOCK to speed things up a bit. Non-critical data.
*/
BEGIN
DECLARE @Result udtPerformanceReturn
SET @Result = 0
/* Test to make sure the date paramaters for a given Index are within the range of dates we actually store */
IF
(
(@BeginDate < (SELECT MIN(SnapDte) FROM dbo.peDHMutualFund WITH (NOLOCK) WHERE id_peMutualFund = @id_MutualFund))
OR
(@EndDate > (SELECT MAX(SnapDte) FROM dbo.peDHMutualFund WITH (NOLOCK) WHERE id_peMutualFund = @id_MutualFund))
)
RETURN NULL --Oops - we don't have data - get outta here!
/*
All data is available - run the Perf calculation
SQL Server will remove any NULLS in the aggregation - null override actions need to be taken
Same as normal Cumulative Performance except for Factor and PortfolioPct
*/
SELECT
@Result = SUM(LOG(((ReturnNum * @FactorNum * @PortfolioPct) + 1)))
FROM
dbo.peDHMutualFund WITH (NOLOCK)
WHERE
id_peMutualFund = @id_MutualFund
AND SnapDte BETWEEN
@BeginDate AND @EndDate
/* OK - translate back to base 10 and return the result */
SET @Result = EXP(@Result) - 1
RETURN @Result
END
Neither of these work - but here they are:
declare @Result udtPerformanceFactor
SET @Result = 0
SELECT
@Result = @Result +
CASE MF.EntityTyp
WHEN 'M' THEN
dbo.peGetFundFactoredPerformance(MF.id_Entity, MF.FactorPct, 60, '7/1/2003', '7/15/2003')
WHEN 'I' THEN
dbo.peGetIndexFactoredPerformance(MF.id_Entity, MF.FactorPct, 60, '7/1/2003', '7/15/2003')
END
FROM
peModelFactor MF
WHERE
MF.id_Model = 1
******************************************************************************************
DECLARE @ID_Entity int
DECLARE @FactorPct udtPerformanceFactor
DECLARE @Result udtPerformanceFactor
SET @Result = 0
DECLARE PERFCURSOR CURSOR LOCAL FORWARD_ONLY FOR
SELECT
MF.id_Entity,
MF.FactorPct
FROM
peModelFactor MF WITH (NOLOCK)
WHERE
MF.id_Model = 1
ORDER BY 1
OPEN PERFCURSOR
FETCH NEXT FROM PERFCURSOR INTO @ID_Entity, @FactorPct
WHILE @@FETCH_STATUS = 0
BEGIN
-- SET @Result = @Result + ISNULL(dbo.peGetFundFactoredPerformance(@ID_Entity, @FactorPct, 60, '7/1/2003', '7/15/2003'), 0)
-- SET @Result = @Result + ISNULL(dbo.peGetFundFactoredPerformance(2, .275, 60, '7/1/2003', '7/15/2003'), 0) --THis seems to work
FETCH NEXT FROM PERFCURSOR INTO @ID_Entity, @FactorPct
END
CLOSE PERFCURSOR
DEALLOCATE PERFCURSOR
SELECT @Result
Also - here's the UDT:
udtPerformanceFactor decimal(19,9)
-
The LOG function with a negative value can cause this error.
Can you step through the code with the debugger in Query Analyzer
Will check it at home tonight(now 9:40am)
-
Thanks 5409045121009...
There's no need to test the UDF - why you ask? Because I've been using the UDF in Production for over 2 months now with no problems.
Problems ONLY OCCUR when trying to call the UDF from a correlated subquery or a cursor.
For what it's worth, all works fine from a cursor when I make this change (beleive it or not):
Change this:
SELECT
@Result = SUM(LOG(((ReturnNum * @FactorNum * @PortfolioPct) + 1)))
To this:
SELECT
@Result = SUM(LOG(((ReturnNum * @PortfolioPct) + 1)))
If I remove the FactorNum variable, it works fine. I've SQL Server to some odd things from time to time - but this is truly the oddest I've ever seen.
- B
-
Heh- here's a new one...
If I replace the "@FactorNum" variable in the calling routing with its actual value from the table ( a hardcoded .275) - all works fine.
So instead of this:
dbo.peGetFundFactoredPerformance(MF.id_Entity, MF.FactorPct, 60, '7/1/2003', '7/15/2003')
I use this:
dbo.peGetFundFactoredPerformance(MF.id_Entity, .275, 60, '7/1/2003', '7/15/2003')
The MF.FactorPct is a UDT of type DECIMAL(19, 9).
This is simply crazy.
I'm sure it has something to do with the LOG function using the FLOAT data type and then it's conversion to a decimal type.
-
Figured it out - finally !!!
My factor percentages SHOULD have been entered in the table like this:
1) .275
2) .55
but I had them as full percentages like this:
1) 27.5
2) 55
So..
A "DOMAIN ERROR" equates to a conversion error or number out of bounds error.
Shees - a whole day gone!!!!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply