Subquery - Order By / Where filter issue

  • 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?

  • 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

    😎

  • 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,

  • 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 > 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?

    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

  • 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

  • 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

    😎

  • 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

    http://dotnetvj.blogspot.com

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply