June 27, 2008 at 9:47 am
I have a query like the following:
Select TableA.*, (Select sum(acres) from p_acres where p_acres.id = TableA.acre_id) As TAcres from TableA Where TAcres > 100
Order by TAcres
Here is my error:
Msg 207, Level 16, State 1, Line 10
Invalid column name 'TAcres'.
Anyone know how to filter data by the subquery?
June 27, 2008 at 9:58 am
Since you posted this in a SQL Server 2005 forum, I am assuming you are using SQL Server 2005. Try this and see if it works for you.
with SumAcres (
acre_id,
TAcres
) as (
select
a.acre_id,
sum(p.acres)
from
dbo.p_acres p
inner join Table_A a
on (p.id = a.acre_id)
)
Select
ta.*,
sa.TAcres
from
TableA ta
inner join SumAcres sa
on (ta.acre_id = sa.acre_id)
Where
sa.TAcres > 100
Order by
sa.TAcres
😎
June 27, 2008 at 9:59 am
Hi,
It's long winded, but you need to use the subquery three times - once in select, once in where and once in order by, i.e.
Select TableA.*,
(Select sum(acres)
from p_acres
where p_acres.id = TableA.acre_id) As TAcres
from TableA
Where (Select sum(acres)
from p_acres
where p_acres.id = TableA.acre_id) > 100
Order by (Select sum(acres)
from p_acres
where p_acres.id = TableA.acre_id)
Technically you could simply 'Order by 2' instead, but that's not particularly good practice.
Hope this helps,
June 27, 2008 at 10:02 am
grtn316 (6/27/2008)
I have a query like the following:
Select TableA.*, (Select sum(acres) from p_acres where p_acres.id = TableA.acre_id) As TAcres from TableA Where TAcres > 100Order by TAcres
Here is my error:
Msg 207, Level 16, State 1, Line 10
Invalid column name 'TAcres'.
Anyone know how to filter data by the subquery?
you can't reference the column alias in the where clause, so you need to fully specify the column i.e.
Select TableA.*, (Select sum(acres) from p_acres where p_acres.id = TableA.acre_id) As TAcres from TableA
Where (Select sum(acres) from p_acres where p_acres.id = TableA.acre_id) > 100
Order by TAcres
Kev
June 27, 2008 at 10:08 am
SELECT a1.*, a2.TAcres
FROM TableA a1
INNER JOIN
(
SELECT acre_id, sum(acres) TAcres
FROM p_acres
GROUP BY acre_id
) a2
ON a1.acre_id = a2.acre_id
WHERE TAcres > 100
ORDER BY TAcres
* Noel
June 27, 2008 at 2:12 pm
Okay, I saw a slight mistake in mine, which I noticed after looking at noeld's version. Mine and noeld are very similiar, his however will also work in SQL Server 2000 where as mine is specific to SQL Server 2005/2008 due to the use of a CTE. Here is my corrected code:
with SumAcres (
acre_id,
TAcres
) as (
select
a.acre_id,
sum(p.acres)
from
dbo.p_acres p
inner join Table_A a
on (p.id = a.acre_id)
group by
p.acre_id
)
Select
ta.*,
sa.TAcres
from
TableA ta
inner join SumAcres sa
on (ta.acre_id = sa.acre_id)
Where
sa.TAcres > 100
Order by
sa.TAcres
😎
June 27, 2008 at 4:47 pm
Hi
I hope you got the answer but i would like to give you basic information.
Select TableA.*, (Select sum(acres) from p_acres where p_acres.id = TableA.acre_id) As TAcres from TableA Where TAcres > 100
Order by TAcres
IN your query you have alias as TAcres and you are using this in WHERE condition and ORDER BY.
In Database you can't use Alias names in WHERE condition. WHERE condition always looks for the columns which are part of tables specified in FROM clause.
In this case alias name is not part of TableA, so raises an error. Where you can use alias names in ORDER BY and HAVING Clauses.
Thanks -- Vj
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply