April 29, 2003 at 8:43 am
Hello...
I have a simple UDF which is part of a query that runds fine. If I take this query, and make it a subquery in the WHERE clause of a larger SQL statement - I get the following error:
Server: Msg 913, Level 16, State 8, Line 3
Could not find database ID 102. Database may not be activated yet or may be in transition.
I know it's the UDF because if I comment it out and replace it with a literal result (the UDF returns a smalldatetime) like a date '3/31/2003' - the whole query operates correctly.
Nothing fancy going on here - no linked servers - just standard DB SQL.
Here's the UDF:
CREATE FUNCTION GetDSSTimeIDFromDate
(
@Date smalldatetime
)
RETURNS int
WITH SCHEMABINDING
AS
/*
Get the DSS Time table ID for a date
*/
BEGIN
DECLARE @Result int
SET @Result =
(
SELECT
id_Time
FROM
dbo.sys_DSSTime WHERE
FullDateDte = @Date
)
RETURN @Result
END
Here's a sample as a SubQuery:
DECLARE @Dater smalldatetime
SET @Dater = '3/31/2003'
SELECT
*
FROM
(
SELECT
'Fund' AS EntityTyp,
MUF.FundNme AS EntityNme,
YearToDate = dbo.peGetFundPerformance
(MUF.id_MutualFund, dbo.GetFirstDayOfYear(@Dater), @Dater) * 100
FROM
cbMutualFund MUF
LEFT JOIN peDHistoryFund HFUN
ON MUF.id_MutualFund = HFUN.ID_MutualFund
AND HFUN.ID_Time = dbo.GetDSSTimeIDFromDate(@Dater)
) DynamicPerformance
If I remove the outer query "SELECT * FROM... DynamicPerformance" - the query runs fine!!! As soon as I wrap the inner query in a basic "Select * from (InnerQuery) Inner" then the statement fails Every time with the same error.
Any ideas????
May 2, 2003 at 8:00 am
This was removed by the editor as SPAM
May 2, 2003 at 11:07 am
I'm not sure about the nesting you are using? You simply select and then subselect with no qualification? What is this query supposed to do or is it just an example?
Darren
Darren
May 2, 2003 at 11:13 am
Sorry...
I am doing an outer select because the inner select actually contains 3 similar select statements which are UNIONed together. And, they need to be sorted with a dynamic sort - so I was going to use as CASE statement as part of the ORDER BY clause.
Regardless of the sorting issue, I'm normally able to wrap ANY query in an outer SELECT statemnet without issue. But, with this one having the UDF as part of the WHERE clause - I run into nothing but problems.
I have not yet tried a simple select with a simnple UDF purely for testing purposes but as my first post states - the UDF is definetely the problem in my example.
- B
May 2, 2003 at 2:58 pm
The code seems very complex and... "not SQL-like". It looks more like coding of nested function calls in a procedural 3GL. This is not really appropriate for SQL. God knows how well SQL compiler and optimiser are handling this stuff. I would recommend rewriting the code without using complex queries. It could be split onto several SQL statements in a stored procedure. Also consider poor performance due to UDF calls that disallow
"set-like" processing.
Sorry if this was not helpful.
May 5, 2003 at 7:07 am
Ouch-
It may look complex but it's really not.
At it's core it's a result set based on several correlated subqueries. I have one chunk of SQL (in a UDF called dbo.peGetFundPerformance) which returns cumulative performance for a financial entity. Just pass in the EntityID, Begin and End Dates, and the UDF returns a scaler performance value. It's non-critical data and is edited only in batch so I'm able to use many WITH (NOLOCK) hints and the performance is actually amazing - super fast.
I think the problem relates to the same udf being called several times with different params within an outer query.
Like I have stated - I'm able to wrap any SELECT statement in an outer SELECT statement without issue. It's just that for some reason, UDF's are causing the compiler problems. Maybe I'll run more tests today and post the results.
- B
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply