October 11, 2011 at 5:18 am
Hello,
I would do something like this
select t1.f1, t2.f1
from table1 t1
join table1 t2
on t2.f5 = t1.f5
and t2.f3 = t1.f3
join table1 t3
on t3.f5 = t1.f5
and t3.f3 = (select t4.f3 from table1 t4 where t4.f4 = min(t2.f4)-1)
and i have this error
Msg 1015, Level 15, State 1, Line 9
An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.
Msg 8121, Level 16, State 1, Line 9
Column 'table1.f4' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
I have never used the having clause
Someone can help me:-D
October 11, 2011 at 5:32 am
chauchs (10/11/2011)
Hello,I would do something like this
select t1.f1, t2.f1from table1 t1
join table1 t2
on t2.f5 = t1.f5
and t2.f3 = t1.f3
join table1 t3
on t3.f5 = t1.f5
and t3.f3 = (select t4.f3 from table1 t4 where t4.f4 = min(t2.f4)-1)
and i have this error
Msg 1015, Level 15, State 1, Line 9
An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.
Msg 8121, Level 16, State 1, Line 9
Column 'table1.f4' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
I have never used the having clause
Someone can help me:-D
So you realize your problem is with this code:
(select t4.f3 from table1 t4 where t4.f4 = min(t2.f4)-1)
I have used a having clause like this:
select t4.f3, count(*) from table1 t4
group by t4.f3
having count(*) > 1
I am not sure how you could get the min value from the outer join using a having clause.
You seem to be joining to the same table over and over again. It might make sense to do a recursive query with a CTE (Common Table Expression).
October 11, 2011 at 8:05 am
chauchs (10/11/2011)
select t1.f1, t2.f1from table1 t1
join table1 t2
on t2.f5 = t1.f5
and t2.f3 = t1.f3
join table1 t3
on t3.f5 = t1.f5
and t3.f3 = (select t4.f3 from table1 t4 where t4.f4 = min(t2.f4)-1)
Are you throwing joins together in some type of science experiment? I would love to hear the explanation of what this is trying to accomplish.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 11, 2011 at 8:35 am
bkubicek (10/11/2011)
I am not sure how you could get the min value from the outer join using a having clause.You seem to be joining to the same table over and over again. It might make sense to do a recursive query with a CTE (Common Table Expression).
You can't. The subquery is evaluated separately--and here is the important part--FOR EACH ROW IN THE OUTER QUERY. As far as the subquery is concerned, there is only one row in the outer query, and any aggregates on that one row are meaningless.
If you need to use aggregates in your joins, you're going to have to pre-calculate the aggregates using CTEs, temp tables, or something similar.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply