|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
what is wrong with this UDF?The column prefix c does not match with a table name . .. what is it that I have done wrong - the query works fine by itself . . . . CREATE FUNCTION dbo.AddWorkDays ( @dte smalldatetime, @NoDays tinyint ) RETURNS smalldatetime AS BEGIN RETURN (SELECT c.dt FROM dbo.Calendar c WHERE c.isWeekday = 1 AND c.isHoliday =0 AND c.dt > @dte AND c.dt <= DATEADD(day,25, @dte) AND @NoDays = ( SELECT COUNT(*) FROM dbo.Calendar c2 WHERE c2.dt >= @dte AND c2.dt <= c.dt AND c2.isWeekday=1 AND c2.isHoliday=0 )) END GO Hi
You don't give the version of SQL Server you are using but I don't get any errors on SQL 2005! It is always useful to post DDL and example data http://www.aspfaq.com/etiquette.asp?id=5006 e.g. CREATE TABLE dbo.Calendar ( dt datetime, isweekday bit, isholiday bit ) INSERT INTO dbo.Calendar ( dt, isweekday, isholiday ) SELECT dt, case WHEN dw between 2 and 6 THEN 1 ELSE 0 END, 0 FROM ( SELECT cast(floor(CAST(dateadd(d,i,getdate()) as float)) as datetime) AS [dt], datepart(dw,dateadd(d,i,getdate())) as [dw] FROM ( SELECT 0 as i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 ) A ) b UPDATE dbo.Calendar SET Isholiday = 1 WHERE dt in ( '20051226', '20051227', '20060102' ) SELECT * FROM dbo.Calendar This may be more concise, of what you want to do! CREATE FUNCTION dbo.AddWorkDays2 ( @dte smalldatetime, @NoDays tinyint ) RETURNS smalldatetime AS BEGIN RETURN ( SELECT c1.dt FROM dbo.Calendar c1 JOIN dbo.Calendar c2 ON c2.dt <= c1.dt AND C2.isholiday = 0 WHERE c1.dt >= @dte AND C1.isholiday = 0 AND c1.isWeekday = 1 GROUP BY c1.dt HAVING SUM(CAST(c2.isWeekday AS int)) = @nodays ) END -- test it! declare @dte smalldatetime declare @nodays int SET @dte = '20051219' SET @nodays = 7 SELECT dbo.AddWorkDays(@dte, @NoDays), dbo.AddWorkDays2(@dte, @NoDays) John Show quote "Al Newbie" <nospam@noidea.com> wrote in message news:eRldARzAGHA.3900@TK2MSFTNGP11.phx.gbl... >I have this UDF but it comes back with the message: > The column prefix c does not match with a table name . .. > > what is it that I have done wrong - the query works fine by itself . . . . > > CREATE FUNCTION dbo.AddWorkDays > ( > @dte smalldatetime, > @NoDays tinyint > ) > RETURNS smalldatetime > AS > BEGIN > RETURN (SELECT c.dt > FROM dbo.Calendar c > WHERE > c.isWeekday = 1 > AND c.isHoliday =0 > AND c.dt > @dte > AND c.dt <= DATEADD(day,25, @dte) > AND @NoDays = ( > SELECT COUNT(*) > FROM dbo.Calendar c2 > WHERE c2.dt >= @dte > AND c2.dt <= c.dt > AND c2.isWeekday=1 > AND c2.isHoliday=0 > )) > END > GO > > Thanks - I am using SQL 2k
I have tried your UDF but again I get the same error: The column prefix 'C1' does not match with a table name or alias name used in the query. and The column prefix 'C2' does not match with a table name or alias name used in the query. Show quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:OlKGs4zAGHA.3352@tk2msftngp13.phx.gbl... > Hi > > You don't give the version of SQL Server you are using but I don't get any > errors on SQL 2005! > > It is always useful to post DDL and example data > http://www.aspfaq.com/etiquette.asp?id=5006 e.g. > CREATE TABLE dbo.Calendar ( dt datetime, isweekday bit, isholiday bit ) > > INSERT INTO dbo.Calendar ( dt, isweekday, isholiday ) > > SELECT dt, case WHEN dw between 2 and 6 THEN 1 ELSE 0 END, 0 > > FROM > > ( SELECT cast(floor(CAST(dateadd(d,i,getdate()) as float)) as datetime) AS > [dt], > > datepart(dw,dateadd(d,i,getdate())) as [dw] > > FROM ( > > SELECT 0 as i > > UNION ALL SELECT 1 > > UNION ALL SELECT 2 > > UNION ALL SELECT 3 > > UNION ALL SELECT 4 > > UNION ALL SELECT 5 > > UNION ALL SELECT 6 > > UNION ALL SELECT 7 > > UNION ALL SELECT 8 > > UNION ALL SELECT 9 > > UNION ALL SELECT 10 > > UNION ALL SELECT 11 > > UNION ALL SELECT 12 > > UNION ALL SELECT 13 > > UNION ALL SELECT 14 > > UNION ALL SELECT 15 > > UNION ALL SELECT 16 > > UNION ALL SELECT 17 > > UNION ALL SELECT 18 > > UNION ALL SELECT 19 ) A ) b > > UPDATE dbo.Calendar > > SET Isholiday = 1 > > WHERE dt in ( '20051226', '20051227', '20060102' ) > > SELECT * FROM dbo.Calendar > > > > This may be more concise, of what you want to do! > > CREATE FUNCTION dbo.AddWorkDays2 > > ( > > @dte smalldatetime, > > @NoDays tinyint > > ) > > RETURNS smalldatetime > > AS > > BEGIN > > RETURN ( > > SELECT c1.dt > > FROM dbo.Calendar c1 > > JOIN dbo.Calendar c2 ON c2.dt <= c1.dt AND C2.isholiday = 0 > > WHERE c1.dt >= @dte > > AND C1.isholiday = 0 > > AND c1.isWeekday = 1 > > GROUP BY c1.dt > > HAVING SUM(CAST(c2.isWeekday AS int)) = @nodays ) > > END > > -- test it! > > declare @dte smalldatetime > > declare @nodays int > > SET @dte = '20051219' > > SET @nodays = 7 > > SELECT dbo.AddWorkDays(@dte, @NoDays), > > dbo.AddWorkDays2(@dte, @NoDays) > > > > John > > "Al Newbie" <nospam@noidea.com> wrote in message > news:eRldARzAGHA.3900@TK2MSFTNGP11.phx.gbl... >>I have this UDF but it comes back with the message: >> The column prefix c does not match with a table name . .. >> >> what is it that I have done wrong - the query works fine by itself . . . >> . >> >> CREATE FUNCTION dbo.AddWorkDays >> ( >> @dte smalldatetime, >> @NoDays tinyint >> ) >> RETURNS smalldatetime >> AS >> BEGIN >> RETURN (SELECT c.dt >> FROM dbo.Calendar c >> WHERE >> c.isWeekday = 1 >> AND c.isHoliday =0 >> AND c.dt > @dte >> AND c.dt <= DATEADD(day,25, @dte) >> AND @NoDays = ( >> SELECT COUNT(*) >> FROM dbo.Calendar c2 >> WHERE c2.dt >= @dte >> AND c2.dt <= c.dt >> AND c2.isWeekday=1 >> AND c2.isHoliday=0 >> )) >> END >> GO >> >> > > Hi
Which service pack/hotfix are you running and how are you calling this? John Show quote "Al Newbie" <nospam@noidea.com> wrote in message news:OpTHTf0AGHA.3520@TK2MSFTNGP10.phx.gbl... > Thanks - I am using SQL 2k > > I have tried your UDF but again I get the same error: > The column prefix 'C1' does not match with a table name or alias name used > in the query. and > The column prefix 'C2' does not match with a table name or alias name used > in the query. > > "John Bell" <jbellnewspo***@hotmail.com> wrote in message > news:OlKGs4zAGHA.3352@tk2msftngp13.phx.gbl... >> Hi >> >> You don't give the version of SQL Server you are using but I don't get >> any errors on SQL 2005! >> >> It is always useful to post DDL and example data >> http://www.aspfaq.com/etiquette.asp?id=5006 e.g. >> CREATE TABLE dbo.Calendar ( dt datetime, isweekday bit, isholiday bit ) >> >> INSERT INTO dbo.Calendar ( dt, isweekday, isholiday ) >> >> SELECT dt, case WHEN dw between 2 and 6 THEN 1 ELSE 0 END, 0 >> >> FROM >> >> ( SELECT cast(floor(CAST(dateadd(d,i,getdate()) as float)) as datetime) >> AS [dt], >> >> datepart(dw,dateadd(d,i,getdate())) as [dw] >> >> FROM ( >> >> SELECT 0 as i >> >> UNION ALL SELECT 1 >> >> UNION ALL SELECT 2 >> >> UNION ALL SELECT 3 >> >> UNION ALL SELECT 4 >> >> UNION ALL SELECT 5 >> >> UNION ALL SELECT 6 >> >> UNION ALL SELECT 7 >> >> UNION ALL SELECT 8 >> >> UNION ALL SELECT 9 >> >> UNION ALL SELECT 10 >> >> UNION ALL SELECT 11 >> >> UNION ALL SELECT 12 >> >> UNION ALL SELECT 13 >> >> UNION ALL SELECT 14 >> >> UNION ALL SELECT 15 >> >> UNION ALL SELECT 16 >> >> UNION ALL SELECT 17 >> >> UNION ALL SELECT 18 >> >> UNION ALL SELECT 19 ) A ) b >> >> UPDATE dbo.Calendar >> >> SET Isholiday = 1 >> >> WHERE dt in ( '20051226', '20051227', '20060102' ) >> >> SELECT * FROM dbo.Calendar >> >> >> >> This may be more concise, of what you want to do! >> >> CREATE FUNCTION dbo.AddWorkDays2 >> >> ( >> >> @dte smalldatetime, >> >> @NoDays tinyint >> >> ) >> >> RETURNS smalldatetime >> >> AS >> >> BEGIN >> >> RETURN ( >> >> SELECT c1.dt >> >> FROM dbo.Calendar c1 >> >> JOIN dbo.Calendar c2 ON c2.dt <= c1.dt AND C2.isholiday = 0 >> >> WHERE c1.dt >= @dte >> >> AND C1.isholiday = 0 >> >> AND c1.isWeekday = 1 >> >> GROUP BY c1.dt >> >> HAVING SUM(CAST(c2.isWeekday AS int)) = @nodays ) >> >> END >> >> -- test it! >> >> declare @dte smalldatetime >> >> declare @nodays int >> >> SET @dte = '20051219' >> >> SET @nodays = 7 >> >> SELECT dbo.AddWorkDays(@dte, @NoDays), >> >> dbo.AddWorkDays2(@dte, @NoDays) >> >> >> >> John >> >> "Al Newbie" <nospam@noidea.com> wrote in message >> news:eRldARzAGHA.3900@TK2MSFTNGP11.phx.gbl... >>>I have this UDF but it comes back with the message: >>> The column prefix c does not match with a table name . .. >>> >>> what is it that I have done wrong - the query works fine by itself . . . >>> . >>> >>> CREATE FUNCTION dbo.AddWorkDays >>> ( >>> @dte smalldatetime, >>> @NoDays tinyint >>> ) >>> RETURNS smalldatetime >>> AS >>> BEGIN >>> RETURN (SELECT c.dt >>> FROM dbo.Calendar c >>> WHERE >>> c.isWeekday = 1 >>> AND c.isHoliday =0 >>> AND c.dt > @dte >>> AND c.dt <= DATEADD(day,25, @dte) >>> AND @NoDays = ( >>> SELECT COUNT(*) >>> FROM dbo.Calendar c2 >>> WHERE c2.dt >= @dte >>> AND c2.dt <= c.dt >>> AND c2.isWeekday=1 >>> AND c2.isHoliday=0 >>> )) >>> END >>> GO >>> >>> >> >> > > SQL 2k Version 8.00.760
I am trying to create the funtion in Query Analyser but it will not let me. The message column prefix .....does not match happens. Show quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:%23LiPAh1AGHA.2708@TK2MSFTNGP12.phx.gbl... > Hi > > Which service pack/hotfix are you running and how are you calling this? > > John > > "Al Newbie" <nospam@noidea.com> wrote in message > news:OpTHTf0AGHA.3520@TK2MSFTNGP10.phx.gbl... >> Thanks - I am using SQL 2k >> >> I have tried your UDF but again I get the same error: >> The column prefix 'C1' does not match with a table name or alias name >> used in the query. and >> The column prefix 'C2' does not match with a table name or alias name >> used in the query. >> >> "John Bell" <jbellnewspo***@hotmail.com> wrote in message >> news:OlKGs4zAGHA.3352@tk2msftngp13.phx.gbl... >>> Hi >>> >>> You don't give the version of SQL Server you are using but I don't get >>> any errors on SQL 2005! >>> >>> It is always useful to post DDL and example data >>> http://www.aspfaq.com/etiquette.asp?id=5006 e.g. >>> CREATE TABLE dbo.Calendar ( dt datetime, isweekday bit, isholiday bit ) >>> >>> INSERT INTO dbo.Calendar ( dt, isweekday, isholiday ) >>> >>> SELECT dt, case WHEN dw between 2 and 6 THEN 1 ELSE 0 END, 0 >>> >>> FROM >>> >>> ( SELECT cast(floor(CAST(dateadd(d,i,getdate()) as float)) as datetime) >>> AS [dt], >>> >>> datepart(dw,dateadd(d,i,getdate())) as [dw] >>> >>> FROM ( >>> >>> SELECT 0 as i >>> >>> UNION ALL SELECT 1 >>> >>> UNION ALL SELECT 2 >>> >>> UNION ALL SELECT 3 >>> >>> UNION ALL SELECT 4 >>> >>> UNION ALL SELECT 5 >>> >>> UNION ALL SELECT 6 >>> >>> UNION ALL SELECT 7 >>> >>> UNION ALL SELECT 8 >>> >>> UNION ALL SELECT 9 >>> >>> UNION ALL SELECT 10 >>> >>> UNION ALL SELECT 11 >>> >>> UNION ALL SELECT 12 >>> >>> UNION ALL SELECT 13 >>> >>> UNION ALL SELECT 14 >>> >>> UNION ALL SELECT 15 >>> >>> UNION ALL SELECT 16 >>> >>> UNION ALL SELECT 17 >>> >>> UNION ALL SELECT 18 >>> >>> UNION ALL SELECT 19 ) A ) b >>> >>> UPDATE dbo.Calendar >>> >>> SET Isholiday = 1 >>> >>> WHERE dt in ( '20051226', '20051227', '20060102' ) >>> >>> SELECT * FROM dbo.Calendar >>> >>> >>> >>> This may be more concise, of what you want to do! >>> >>> CREATE FUNCTION dbo.AddWorkDays2 >>> >>> ( >>> >>> @dte smalldatetime, >>> >>> @NoDays tinyint >>> >>> ) >>> >>> RETURNS smalldatetime >>> >>> AS >>> >>> BEGIN >>> >>> RETURN ( >>> >>> SELECT c1.dt >>> >>> FROM dbo.Calendar c1 >>> >>> JOIN dbo.Calendar c2 ON c2.dt <= c1.dt AND C2.isholiday = 0 >>> >>> WHERE c1.dt >= @dte >>> >>> AND C1.isholiday = 0 >>> >>> AND c1.isWeekday = 1 >>> >>> GROUP BY c1.dt >>> >>> HAVING SUM(CAST(c2.isWeekday AS int)) = @nodays ) >>> >>> END >>> >>> -- test it! >>> >>> declare @dte smalldatetime >>> >>> declare @nodays int >>> >>> SET @dte = '20051219' >>> >>> SET @nodays = 7 >>> >>> SELECT dbo.AddWorkDays(@dte, @NoDays), >>> >>> dbo.AddWorkDays2(@dte, @NoDays) >>> >>> >>> >>> John >>> >>> "Al Newbie" <nospam@noidea.com> wrote in message >>> news:eRldARzAGHA.3900@TK2MSFTNGP11.phx.gbl... >>>>I have this UDF but it comes back with the message: >>>> The column prefix c does not match with a table name . .. >>>> >>>> what is it that I have done wrong - the query works fine by itself . . >>>> . . >>>> >>>> CREATE FUNCTION dbo.AddWorkDays >>>> ( >>>> @dte smalldatetime, >>>> @NoDays tinyint >>>> ) >>>> RETURNS smalldatetime >>>> AS >>>> BEGIN >>>> RETURN (SELECT c.dt >>>> FROM dbo.Calendar c >>>> WHERE >>>> c.isWeekday = 1 >>>> AND c.isHoliday =0 >>>> AND c.dt > @dte >>>> AND c.dt <= DATEADD(day,25, @dte) >>>> AND @NoDays = ( >>>> SELECT COUNT(*) >>>> FROM dbo.Calendar c2 >>>> WHERE c2.dt >= @dte >>>> AND c2.dt <= c.dt >>>> AND c2.isWeekday=1 >>>> AND c2.isHoliday=0 >>>> )) >>>> END >>>> GO >>>> >>>> >>> >>> >> >> > > Looks to me as a bug; try something like:
CREATE FUNCTION dbo.AddWorkDays ( @dte smalldatetime, @NoDays tinyint ) RETURNS smalldatetime AS BEGIN declare @dt2 smalldatetime SELECT @dt2 = c.dt FROM dbo.Calendar c WHERE c.isWeekday = 1 AND c.isHoliday =0 AND c.dt > @dte AND c.dt <= DATEADD(day,25, @dte) AND @NoDays = ( SELECT COUNT(*) FROM dbo.Calendar c2 WHERE c2.dt >= @dte AND c2.dt <= c.dt AND c2.isWeekday=1 AND c2.isHoliday=0 ) return @dt2 END Also, the expression should probably be « c2.dt > @dte » and not « c2.dt >= @dte » in the second select statement; otherwise you function won't work for most days of the week when the @NoDays = 1. Show quote "Al Newbie" <nospam@noidea.com> wrote in message news:eRldARzAGHA.3900@TK2MSFTNGP11.phx.gbl... >I have this UDF but it comes back with the message: > The column prefix c does not match with a table name . .. > > what is it that I have done wrong - the query works fine by itself . . . . > > CREATE FUNCTION dbo.AddWorkDays > ( > @dte smalldatetime, > @NoDays tinyint > ) > RETURNS smalldatetime > AS > BEGIN > RETURN (SELECT c.dt > FROM dbo.Calendar c > WHERE > c.isWeekday = 1 > AND c.isHoliday =0 > AND c.dt > @dte > AND c.dt <= DATEADD(day,25, @dte) > AND @NoDays = ( > SELECT COUNT(*) > FROM dbo.Calendar c2 > WHERE c2.dt >= @dte > AND c2.dt <= c.dt > AND c2.isWeekday=1 > AND c2.isHoliday=0 > )) > END > GO > > On Sat, 17 Dec 2005 18:03:55 -0000, Al Newbie wrote:
>I have this UDF but it comes back with the message: Hi Al,>The column prefix c does not match with a table name . .. > >what is it that I have done wrong - the query works fine by itself . . . . In a scalar UDF, the RETURN can only accept a scalar_expression. A query is not a scalar expression (though it can be converted to one in some situations). The code below will work: CREATE FUNCTION dbo.AddWorkDays ( @dte smalldatetime, @NoDays tinyint ) RETURNS smalldatetime AS BEGIN DECLARE @x smalldatetime SET @x = (SELECT c.dt FROM dbo.Calendar c WHERE c.isWeekday = 1 AND c.isHoliday =0 AND c.dt > @dte AND c.dt <= DATEADD(day,25, @dte) AND @NoDays = ( SELECT COUNT(*) FROM dbo.Calendar c2 WHERE c2.dt >= @dte AND c2.dt <= c.dt AND c2.isWeekday=1 AND c2.isHoliday=0 )) RETURN @x END GO NOTE: This code looks familiar :-) Don't forget that the 25 in the DATEADD function is just to set some upper limit on the amount of rows to check; it should be large enough to make sure that you'l always get correct results. For instance, if you call this function with @NoDays equal to 20, you'll get nothing returned... Consider changing this line to AND c.dt <= DATEADD(day, @NoDays * 2 + 5, @dte) to make sure that you'll always get the correct result for each value of @NoDays. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hugo - you are a star!
Thanks Ali Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:ila9q194f9992pq5tnpc47ra4a6n4f86qq@4ax.com... > On Sat, 17 Dec 2005 18:03:55 -0000, Al Newbie wrote: > >>I have this UDF but it comes back with the message: >>The column prefix c does not match with a table name . .. >> >>what is it that I have done wrong - the query works fine by itself . . . . > > Hi Al, > > In a scalar UDF, the RETURN can only accept a scalar_expression. A query > is not a scalar expression (though it can be converted to one in some > situations). > > The code below will work: > > CREATE FUNCTION dbo.AddWorkDays > ( > @dte smalldatetime, > @NoDays tinyint > ) > RETURNS smalldatetime > AS > BEGIN > DECLARE @x smalldatetime > SET @x = (SELECT c.dt > FROM dbo.Calendar c > WHERE > c.isWeekday = 1 > AND c.isHoliday =0 > AND c.dt > @dte > AND c.dt <= DATEADD(day,25, @dte) > AND @NoDays = ( > SELECT COUNT(*) > FROM dbo.Calendar c2 > WHERE c2.dt >= @dte > AND c2.dt <= c.dt > AND c2.isWeekday=1 > AND c2.isHoliday=0 > )) > RETURN @x > END > GO > > NOTE: This code looks familiar :-) Don't forget that the 25 in the > DATEADD function is just to set some upper limit on the amount of rows > to check; it should be large enough to make sure that you'l always get > correct results. For instance, if you call this function with @NoDays > equal to 20, you'll get nothing returned... > > Consider changing this line to > AND c.dt <= DATEADD(day, @NoDays * 2 + 5, @dte) > to make sure that you'll always get the correct result for each value of > @NoDays. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) Al,
I agree with Sylvain that this is a bug. The C1 and C2 errors you got with John's suggestion are because you have a case-sensitive collation and he does not. If you correct the spelling of C1 to c1 and C2 to c2, his suggestion will work. I'll add that I didn't look closely enough to see if his suggestion would work correctly (that is, do the same thing your query should do). If I can't find previous reports of this bug, I'll report it to Microsoft. Steve Kass Drew University Al Newbie wrote: Show quote >I have this UDF but it comes back with the message: >The column prefix c does not match with a table name . .. > >what is it that I have done wrong - the query works fine by itself . . . . > >CREATE FUNCTION dbo.AddWorkDays >( > @dte smalldatetime, > @NoDays tinyint >) >RETURNS smalldatetime >AS >BEGIN >RETURN (SELECT c.dt > FROM dbo.Calendar c > WHERE > c.isWeekday = 1 > AND c.isHoliday =0 > AND c.dt > @dte > AND c.dt <= DATEADD(day,25, @dte) > AND @NoDays = ( > SELECT COUNT(*) > FROM dbo.Calendar c2 > WHERE c2.dt >= @dte > AND c2.dt <= c.dt > AND c2.isWeekday=1 > AND c2.isHoliday=0 > )) >END >GO > > > > It is possible that the actual code was not as clean as that posted!!
John Steve Kass wrote: Show quote > Al, > > I agree with Sylvain that this is a bug. The C1 and C2 > errors you got with John's suggestion are because you > have a case-sensitive collation and he does not. If you > correct the spelling of C1 to c1 and C2 to c2, his suggestion > will work. > > I'll add that I didn't look closely enough to see if his suggestion > would work correctly (that is, do the same thing your query > should do). > > If I can't find previous reports of this bug, I'll report it to > Microsoft. > > Steve Kass > Drew University > > Al Newbie wrote: > > >I have this UDF but it comes back with the message: > >The column prefix c does not match with a table name . .. > > > >what is it that I have done wrong - the query works fine by itself . . . . > > > >CREATE FUNCTION dbo.AddWorkDays > >( > > @dte smalldatetime, > > @NoDays tinyint > >) > >RETURNS smalldatetime > >AS > >BEGIN > >RETURN (SELECT c.dt > > FROM dbo.Calendar c > > WHERE > > c.isWeekday = 1 > > AND c.isHoliday =0 > > AND c.dt > @dte > > AND c.dt <= DATEADD(day,25, @dte) > > AND @NoDays = ( > > SELECT COUNT(*) > > FROM dbo.Calendar c2 > > WHERE c2.dt >= @dte > > AND c2.dt <= c.dt > > AND c2.isWeekday=1 > > AND c2.isHoliday=0 > > )) > >END > >GO > > > > > > > > I just copied and pasted your code.
Hugo seems to have found the answer i.e. In a scalar UDF, the RETURN can only accept a scalar_expression - this seems to do the trick. Thanks for your help Al Show quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:1134911692.468225.16720@g43g2000cwa.googlegroups.com... > It is possible that the actual code was not as clean as that posted!! > > John > > Steve Kass wrote: >> Al, >> >> I agree with Sylvain that this is a bug. The C1 and C2 >> errors you got with John's suggestion are because you >> have a case-sensitive collation and he does not. If you >> correct the spelling of C1 to c1 and C2 to c2, his suggestion >> will work. >> >> I'll add that I didn't look closely enough to see if his suggestion >> would work correctly (that is, do the same thing your query >> should do). >> >> If I can't find previous reports of this bug, I'll report it to >> Microsoft. >> >> Steve Kass >> Drew University >> >> Al Newbie wrote: >> >> >I have this UDF but it comes back with the message: >> >The column prefix c does not match with a table name . .. >> > >> >what is it that I have done wrong - the query works fine by itself . . . >> >. >> > >> >CREATE FUNCTION dbo.AddWorkDays >> >( >> > @dte smalldatetime, >> > @NoDays tinyint >> >) >> >RETURNS smalldatetime >> >AS >> >BEGIN >> >RETURN (SELECT c.dt >> > FROM dbo.Calendar c >> > WHERE >> > c.isWeekday = 1 >> > AND c.isHoliday =0 >> > AND c.dt > @dte >> > AND c.dt <= DATEADD(day,25, @dte) >> > AND @NoDays = ( >> > SELECT COUNT(*) >> > FROM dbo.Calendar c2 >> > WHERE c2.dt >= @dte >> > AND c2.dt <= c.dt >> > AND c2.isWeekday=1 >> > AND c2.isHoliday=0 >> > )) >> >END >> >GO >> > >> > >> > >> > > On Sun, 18 Dec 2005 22:28:08 -0000, Al Newbie wrote:
>I just copied and pasted your code. Hi Al,> >Hugo seems to have found the answer i.e. In a scalar UDF, the RETURN can >only accept a scalar_expression - this seems to do the trick. I found a working version of your code, but the explanation I gave was incorrect. At the time I wrote it, it seemed logical. But after reading Steve's post, I tried John's code. After correcting the uppercase/lowercase differences, the CREATE FUNCTION statement executed without error. Apparently, a RETURN in a scalar UDF *can* accept a subquery, just not the subquery in your original UDF. I can only agree with Steve that this smells like a bug. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hi
You may also want to check your data. John Show quote "Al Newbie" <nospam@noidea.com> wrote in message news:%2381KXJCBGHA.412@TK2MSFTNGP15.phx.gbl... >I just copied and pasted your code. > > Hugo seems to have found the answer i.e. In a scalar UDF, the RETURN can > only accept a scalar_expression - this seems to do the trick. > > Thanks for your help > > Al > "John Bell" <jbellnewspo***@hotmail.com> wrote in message > news:1134911692.468225.16720@g43g2000cwa.googlegroups.com... >> It is possible that the actual code was not as clean as that posted!! >> >> John >> >> Steve Kass wrote: >>> Al, >>> >>> I agree with Sylvain that this is a bug. The C1 and C2 >>> errors you got with John's suggestion are because you >>> have a case-sensitive collation and he does not. If you >>> correct the spelling of C1 to c1 and C2 to c2, his suggestion >>> will work. >>> >>> I'll add that I didn't look closely enough to see if his suggestion >>> would work correctly (that is, do the same thing your query >>> should do). >>> >>> If I can't find previous reports of this bug, I'll report it to >>> Microsoft. >>> >>> Steve Kass >>> Drew University >>> >>> Al Newbie wrote: >>> >>> >I have this UDF but it comes back with the message: >>> >The column prefix c does not match with a table name . .. >>> > >>> >what is it that I have done wrong - the query works fine by itself . . >>> >. . >>> > >>> >CREATE FUNCTION dbo.AddWorkDays >>> >( >>> > @dte smalldatetime, >>> > @NoDays tinyint >>> >) >>> >RETURNS smalldatetime >>> >AS >>> >BEGIN >>> >RETURN (SELECT c.dt >>> > FROM dbo.Calendar c >>> > WHERE >>> > c.isWeekday = 1 >>> > AND c.isHoliday =0 >>> > AND c.dt > @dte >>> > AND c.dt <= DATEADD(day,25, @dte) >>> > AND @NoDays = ( >>> > SELECT COUNT(*) >>> > FROM dbo.Calendar c2 >>> > WHERE c2.dt >= @dte >>> > AND c2.dt <= c.dt >>> > AND c2.isWeekday=1 >>> > AND c2.isHoliday=0 >>> > )) >>> >END >>> >GO >>> > >>> > >>> > >>> > >> > > I have tried your code and made the changes to C1 and C2 and it does work -
if I make the change to the orignial subquery then that also works. I will now check that your different function returns the same results - should your code return the results quicker? Thanks again for your help Show quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:OMPmhmHBGHA.4084@TK2MSFTNGP12.phx.gbl... > Hi > > You may also want to check your data. > > John > > "Al Newbie" <nospam@noidea.com> wrote in message > news:%2381KXJCBGHA.412@TK2MSFTNGP15.phx.gbl... >>I just copied and pasted your code. >> >> Hugo seems to have found the answer i.e. In a scalar UDF, the RETURN can >> only accept a scalar_expression - this seems to do the trick. >> >> Thanks for your help >> >> Al >> "John Bell" <jbellnewspo***@hotmail.com> wrote in message >> news:1134911692.468225.16720@g43g2000cwa.googlegroups.com... >>> It is possible that the actual code was not as clean as that posted!! >>> >>> John >>> >>> Steve Kass wrote: >>>> Al, >>>> >>>> I agree with Sylvain that this is a bug. The C1 and C2 >>>> errors you got with John's suggestion are because you >>>> have a case-sensitive collation and he does not. If you >>>> correct the spelling of C1 to c1 and C2 to c2, his suggestion >>>> will work. >>>> >>>> I'll add that I didn't look closely enough to see if his suggestion >>>> would work correctly (that is, do the same thing your query >>>> should do). >>>> >>>> If I can't find previous reports of this bug, I'll report it to >>>> Microsoft. >>>> >>>> Steve Kass >>>> Drew University >>>> >>>> Al Newbie wrote: >>>> >>>> >I have this UDF but it comes back with the message: >>>> >The column prefix c does not match with a table name . .. >>>> > >>>> >what is it that I have done wrong - the query works fine by itself . . >>>> >. . >>>> > >>>> >CREATE FUNCTION dbo.AddWorkDays >>>> >( >>>> > @dte smalldatetime, >>>> > @NoDays tinyint >>>> >) >>>> >RETURNS smalldatetime >>>> >AS >>>> >BEGIN >>>> >RETURN (SELECT c.dt >>>> > FROM dbo.Calendar c >>>> > WHERE >>>> > c.isWeekday = 1 >>>> > AND c.isHoliday =0 >>>> > AND c.dt > @dte >>>> > AND c.dt <= DATEADD(day,25, @dte) >>>> > AND @NoDays = ( >>>> > SELECT COUNT(*) >>>> > FROM dbo.Calendar c2 >>>> > WHERE c2.dt >= @dte >>>> > AND c2.dt <= c.dt >>>> > AND c2.isWeekday=1 >>>> > AND c2.isHoliday=0 >>>> > )) >>>> >END >>>> >GO >>>> > >>>> > >>>> > >>>> > >>> >> >> > > I have now tested your suggestion and whilst it does return the same result
it takes 50 seconds to run whereas the original subquery takes less than 1 second. Thanks again Show quote "Newbie" <nospam@noidea.com> wrote in message news:eFv%23MiIBGHA.516@TK2MSFTNGP15.phx.gbl... >I have tried your code and made the changes to C1 and C2 and it does work - >if I make the change to the orignial subquery then that also works. > I will now check that your different function returns the same results - > should your code return the results quicker? > Thanks again for your help > "John Bell" <jbellnewspo***@hotmail.com> wrote in message > news:OMPmhmHBGHA.4084@TK2MSFTNGP12.phx.gbl... >> Hi >> >> You may also want to check your data. >> >> John >> >> "Al Newbie" <nospam@noidea.com> wrote in message >> news:%2381KXJCBGHA.412@TK2MSFTNGP15.phx.gbl... >>>I just copied and pasted your code. >>> >>> Hugo seems to have found the answer i.e. In a scalar UDF, the RETURN can >>> only accept a scalar_expression - this seems to do the trick. >>> >>> Thanks for your help >>> >>> Al >>> "John Bell" <jbellnewspo***@hotmail.com> wrote in message >>> news:1134911692.468225.16720@g43g2000cwa.googlegroups.com... >>>> It is possible that the actual code was not as clean as that posted!! >>>> >>>> John >>>> >>>> Steve Kass wrote: >>>>> Al, >>>>> >>>>> I agree with Sylvain that this is a bug. The C1 and C2 >>>>> errors you got with John's suggestion are because you >>>>> have a case-sensitive collation and he does not. If you >>>>> correct the spelling of C1 to c1 and C2 to c2, his suggestion >>>>> will work. >>>>> >>>>> I'll add that I didn't look closely enough to see if his suggestion >>>>> would work correctly (that is, do the same thing your query >>>>> should do). >>>>> >>>>> If I can't find previous reports of this bug, I'll report it to >>>>> Microsoft. >>>>> >>>>> Steve Kass >>>>> Drew University >>>>> >>>>> Al Newbie wrote: >>>>> >>>>> >I have this UDF but it comes back with the message: >>>>> >The column prefix c does not match with a table name . .. >>>>> > >>>>> >what is it that I have done wrong - the query works fine by itself . >>>>> >. . . >>>>> > >>>>> >CREATE FUNCTION dbo.AddWorkDays >>>>> >( >>>>> > @dte smalldatetime, >>>>> > @NoDays tinyint >>>>> >) >>>>> >RETURNS smalldatetime >>>>> >AS >>>>> >BEGIN >>>>> >RETURN (SELECT c.dt >>>>> > FROM dbo.Calendar c >>>>> > WHERE >>>>> > c.isWeekday = 1 >>>>> > AND c.isHoliday =0 >>>>> > AND c.dt > @dte >>>>> > AND c.dt <= DATEADD(day,25, @dte) >>>>> > AND @NoDays = ( >>>>> > SELECT COUNT(*) >>>>> > FROM dbo.Calendar c2 >>>>> > WHERE c2.dt >= @dte >>>>> > AND c2.dt <= c.dt >>>>> > AND c2.isWeekday=1 >>>>> > AND c2.isHoliday=0 >>>>> > )) >>>>> >END >>>>> >GO >>>>> > >>>>> > >>>>> > >>>>> > >>>> >>> >>> >> >> > > You may want to look at limiting the scope of the data being used as pre
your original function (although this would reduce the functionalilty) and also check out the query plans to see if it will benefit with another index. John Show quote "Newbie" <nospam@noidea.com> wrote in message news:en4mSnIBGHA.1676@TK2MSFTNGP09.phx.gbl... >I have now tested your suggestion and whilst it does return the same result >it takes 50 seconds to run whereas the original subquery takes less than 1 >second. > Thanks again > "Newbie" <nospam@noidea.com> wrote in message > news:eFv%23MiIBGHA.516@TK2MSFTNGP15.phx.gbl... >>I have tried your code and made the changes to C1 and C2 and it does >>work - if I make the change to the orignial subquery then that also works. >> I will now check that your different function returns the same results - >> should your code return the results quicker? >> Thanks again for your help >> "John Bell" <jbellnewspo***@hotmail.com> wrote in message >> news:OMPmhmHBGHA.4084@TK2MSFTNGP12.phx.gbl... >>> Hi >>> >>> You may also want to check your data. >>> >>> John >>> >>> "Al Newbie" <nospam@noidea.com> wrote in message >>> news:%2381KXJCBGHA.412@TK2MSFTNGP15.phx.gbl... >>>>I just copied and pasted your code. >>>> >>>> Hugo seems to have found the answer i.e. In a scalar UDF, the RETURN >>>> can only accept a scalar_expression - this seems to do the trick. >>>> >>>> Thanks for your help >>>> >>>> Al >>>> "John Bell" <jbellnewspo***@hotmail.com> wrote in message >>>> news:1134911692.468225.16720@g43g2000cwa.googlegroups.com... >>>>> It is possible that the actual code was not as clean as that posted!! >>>>> >>>>> John >>>>> >>>>> Steve Kass wrote: >>>>>> Al, >>>>>> >>>>>> I agree with Sylvain that this is a bug. The C1 and C2 >>>>>> errors you got with John's suggestion are because you >>>>>> have a case-sensitive collation and he does not. If you >>>>>> correct the spelling of C1 to c1 and C2 to c2, his suggestion >>>>>> will work. >>>>>> >>>>>> I'll add that I didn't look closely enough to see if his suggestion >>>>>> would work correctly (that is, do the same thing your query >>>>>> should do). >>>>>> >>>>>> If I can't find previous reports of this bug, I'll report it to >>>>>> Microsoft. >>>>>> >>>>>> Steve Kass >>>>>> Drew University >>>>>> >>>>>> Al Newbie wrote: >>>>>> >>>>>> >I have this UDF but it comes back with the message: >>>>>> >The column prefix c does not match with a table name . .. >>>>>> > >>>>>> >what is it that I have done wrong - the query works fine by itself . >>>>>> >. . . >>>>>> > >>>>>> >CREATE FUNCTION dbo.AddWorkDays >>>>>> >( >>>>>> > @dte smalldatetime, >>>>>> > @NoDays tinyint >>>>>> >) >>>>>> >RETURNS smalldatetime >>>>>> >AS >>>>>> >BEGIN >>>>>> >RETURN (SELECT c.dt >>>>>> > FROM dbo.Calendar c >>>>>> > WHERE >>>>>> > c.isWeekday = 1 >>>>>> > AND c.isHoliday =0 >>>>>> > AND c.dt > @dte >>>>>> > AND c.dt <= DATEADD(day,25, @dte) >>>>>> > AND @NoDays = ( >>>>>> > SELECT COUNT(*) >>>>>> > FROM dbo.Calendar c2 >>>>>> > WHERE c2.dt >= @dte >>>>>> > AND c2.dt <= c.dt >>>>>> > AND c2.isWeekday=1 >>>>>> > AND c2.isHoliday=0 >>>>>> > )) >>>>>> >END >>>>>> >GO >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics