Using UDFs in a SELECT
-
Hello, I am trying to generate some statistics from my SQL Server 2000 database. I have two UDFs, one which determines which days have complete data for a range of dates, and the other generates the statistics for a particular day. I am fustrated because SQL Server doesn't seem to want to allow me to use these functions as I would like. I even dumped the contents of the dates function into a table variable so I do something like:
DECLARE @dates TABLE([date] smalldatetime)
INSERT INTO @dates
SELECT [date] FROM dbo.dates_with_complete_data(@start_date, @end_date)The above works fine, but if I try an use that as input to the other function I get errors.
SELECT calc.*
FROM @dates as fd
INNER JOIN (dbo.calculation(fd.[date])) as calc
ON cc.[date] = fd.[date]Can anyone shed some light on how I might tackle this? If I remain stuck I might have to do the unthinkable and create my first ever cursor! :eek: --Colin Mackay--
EuroCPian Spring 2004 Get Together[^] "You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
-
Hello, I am trying to generate some statistics from my SQL Server 2000 database. I have two UDFs, one which determines which days have complete data for a range of dates, and the other generates the statistics for a particular day. I am fustrated because SQL Server doesn't seem to want to allow me to use these functions as I would like. I even dumped the contents of the dates function into a table variable so I do something like:
DECLARE @dates TABLE([date] smalldatetime)
INSERT INTO @dates
SELECT [date] FROM dbo.dates_with_complete_data(@start_date, @end_date)The above works fine, but if I try an use that as input to the other function I get errors.
SELECT calc.*
FROM @dates as fd
INNER JOIN (dbo.calculation(fd.[date])) as calc
ON cc.[date] = fd.[date]Can anyone shed some light on how I might tackle this? If I remain stuck I might have to do the unthinkable and create my first ever cursor! :eek: --Colin Mackay--
EuroCPian Spring 2004 Get Together[^] "You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
-
I suspect the problem is in the inner join statement. Try this: SELECT calc.* FROM @dates as fd INNER JOIN ( SELECT * FROM (dbo.calculation(fd.[date]))) as calc ON cc.[date] = fd.[date] Barbara :cool:
Thanks for your response, however that doesn't work either. I think it may because of the circular nature of what I am trying to do. The calculation does not return any rows unless it has a date, the join doesn't know what dates match because the calculation has not returned any rows, so no dates can be plugged into the function. The error message I get is "Incorrect syntax near 'fd'." giving the line number of where I call the UDF. --Colin Mackay--
EuroCPian Spring 2004 Get Together[^] "You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
-
Thanks for your response, however that doesn't work either. I think it may because of the circular nature of what I am trying to do. The calculation does not return any rows unless it has a date, the join doesn't know what dates match because the calculation has not returned any rows, so no dates can be plugged into the function. The error message I get is "Incorrect syntax near 'fd'." giving the line number of where I call the UDF. --Colin Mackay--
EuroCPian Spring 2004 Get Together[^] "You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
-
Hello, I am trying to generate some statistics from my SQL Server 2000 database. I have two UDFs, one which determines which days have complete data for a range of dates, and the other generates the statistics for a particular day. I am fustrated because SQL Server doesn't seem to want to allow me to use these functions as I would like. I even dumped the contents of the dates function into a table variable so I do something like:
DECLARE @dates TABLE([date] smalldatetime)
INSERT INTO @dates
SELECT [date] FROM dbo.dates_with_complete_data(@start_date, @end_date)The above works fine, but if I try an use that as input to the other function I get errors.
SELECT calc.*
FROM @dates as fd
INNER JOIN (dbo.calculation(fd.[date])) as calc
ON cc.[date] = fd.[date]Can anyone shed some light on how I might tackle this? If I remain stuck I might have to do the unthinkable and create my first ever cursor! :eek: --Colin Mackay--
EuroCPian Spring 2004 Get Together[^] "You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The only advice I can give is to dump the dbo.calculation function, and compute the various parts in the SELECT clause instead, probably using separate functions. I believe SQL Server is able to look inside a function when compiling a query plan, so it may be able to perform whatever operations you have specified in parallel anyway.