December 19, 2007 at 10:22 pm
Hi,
when I run this query...
SELECT mer_from_dttm fd,
DBO.FreqFortnightlyFunction('24-MAY-2006 2:40:00', '24-MAY-2006 2:40:00')
from mea_req_41_anni
group by mer_from_dttm,
DBO.FreqFortnightlyFunction('24-MAY-2006 2:40:00', '24-MAY-2006 2:40:00')
I get the exception :
Each GROUP BY expression must contain at least one column that is not an outer reference.
The FreqFortnightlyFunction is a function:
CREATE FUNCTION DBO.FreqFortnightlyFunction(@CALCDATE datetime, @PIVOTDATE datetime)
RETURNS DATETIME
AS
BEGIN
DECLARE @RESULT DATETIME
DECLARE @TEMP DATETIME
DECLARE @DIFF INT
SET @TEMP = CONVERT(DATETIME,(CONVERT(VARCHAR, DATEPART(YEAR, @CALCDATE)) + '-' + CONVERT(VARCHAR, DATEPART(MONTH, @CALCDATE))
+ '-' + CONVERT(VARCHAR, DATEPART(DAY, @CALCDATE)) + ' ' + CONVERT(VARCHAR, DATEPART(HOUR, @PIVOTDATE))
+':' + CONVERT(VARCHAR, DATEPART(MINUTE, @PIVOTDATE)) + ':' + CONVERT(VARCHAR, DATEPART(SECOND, @PIVOTDATE))))
SET @TEMP = DATEADD(DAY, (DATEPART(WEEKDAY, @PIVOTDATE) + 7 - DATEPART(WEEKDAY, @TEMP)) % 7, @TEMP)
IF @TEMP > @CALCDATE
BEGIN
SET @RESULT = @TEMP
END
ELSE
BEGIN
SET @RESULT = DATEADD(DAY, 7, @TEMP)
END
SET @DIFF = DATEPART(DAY, @PIVOTDATE - @RESULT)
IF (@DIFF % 2) <> 0
BEGIN
SET @RESULT = DATEADD(DAY, 7, @RESULT) + dateadd(ss, -1 ,0)
END
RETURN @RESULT
END
GO
Can anyone please tell me why the above query is throwing this exception although i'm not using any joins or nested queries...
Thanks.
December 20, 2007 at 12:39 am
Why is the group by there at all? You aren't doing any form of aggregation that could require a group by.
If you're trying to eliminate duplicate rows, rather use Distinct.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2007 at 12:50 am
GilaMonster (12/20/2007)
Why is the group by there at all? You aren't doing any form of aggregation that could require a group by.If you're trying to eliminate duplicate rows, rather use Distinct.
i've added just 2 columns for testing if the query works... in the actual query, I have many more columns that are not part of the group by clause.
Is this a restriction from SQL Server that we cannot call a function in the group by clause. If Yes, is there an alternative solution or a work around for this problem of mine.
Thanks,
Roopa.
December 20, 2007 at 9:06 am
You can use an inline function in a group by clause. I've done so. (It kills performance, but if it's necessary, it's necessary.)
Try running the select without the function (remove from both Select and Group By clauses), and see if the error goes away. If so, then the function is the problem. If not, then it's not the problem. That will at least narrow down what you need to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 20, 2007 at 11:06 am
You cant put a function in a group by unless you set a column value = to it.
example:
SELECT mer_from_dttm fd,
column2 = DBO.FreqFortnightlyFunction('24-MAY-2006 2:40:00', '24-MAY-2006 2:40:00')
from mea_req_41_anni
group by mer_from_dttm, column2
December 20, 2007 at 11:16 am
Adam Haines (12/20/2007)
You cant put a function in a group by unless you set a column value = to it.example:
SELECT mer_from_dttm fd,
column2 = DBO.FreqFortnightlyFunction('24-MAY-2006 2:40:00', '24-MAY-2006 2:40:00')
from mea_req_41_anni
group by mer_from_dttm, column2
That will return "Invalid column name 'column2'"
Reason is, at the point that the group by runs, the aliases in the select have not been evaluated. Only the order by can use column aliases. See with the following
SELECT name fd,
column2 = DateDiff(dd,'24-MAY-2006 2:40:00', '24-MAY-2006 2:40:00')
from sys.objects
group by name, column2
I think it might be because the function doesn't take any column as a parameter, and it's not referencing any data in it.
Try this perhaps
SELECT *, DBO.FreqFortnightlyFunction('24-MAY-2006 2:40:00', '24-MAY-2006 2:40:00') FROM
(SELECT mer_from_dttm fd
from mea_req_41_anni
group by mer_from_dttm) GroupedSubQuery
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2007 at 11:25 am
GilaMonster,
You are absolutely correct. I forgot to mention that "column2" has to be an actual column in the table for this work. You cannot do this with column aliases.
Thanks for clarifiy my post.
Adam
December 21, 2007 at 12:17 am
Adam Haines (12/20/2007)
GilaMonster,You are absolutely correct. I forgot to mention that "column2" has to be an actual column in the table for this work. You cannot do this with column aliases.
Interesting trick.
Of course, since the group by will be done on the original column, not the aliased function, it may not always give you what you're expecting.
SELECT NAME = DateDiff(dd,'24-MAY-2006 2:40:00', '24-MAY-2006 2:40:00')
from sys.indexes
group by NAME
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2007 at 9:13 pm
Thanks to all of you for your help. I found the problem in my query.
It was expecting a column name in the group by clause. Since I had hardcoded the date here, I was getting the error. The following query executes perfectly...
select count(a), count(b), mer_from_dttm, DBO.FreqFortnightlyFunction(mer_from_dttm, mer_from_dttm)
from mea_req_41 fd
group by mer_from_dttm, DBO.FreqFortnightlyFunction(mer_from_dttm, mer_from_dttm)
Thanks again, for all the help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply