Newbie - Whats wrong with following?

Hi,

This simple code using the Northwind db and SQL 2000...when I have the 2nd
from botton line commented out as I do now it works well and give me a
summary of the orders and totals from the [order details] table, nothing
special there.

However, if I uncomment the 'where ordervalue > 500' line and run it I get
an error that says "Invalid column name 'ordervalue' "

Any ideas?

Thanks,

td.

select
o.orderid
,o.orderdate
,c.companyname
,sum(unitprice * quantity) as ordervalue
from
orders o
join
customers c
on
o.customerid = c.customerid
join
[order details] od
on o.orderid = od.orderid
--where ordervalue > 500
group by o.orderid, c.companyname, o.orderdate"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:3b0pjhF6d9fg9U1@.individual.net...
> Hi,
> This simple code using the Northwind db and SQL 2000...when I have the 2nd
> from botton line commented out as I do now it works well and give me a
> summary of the orders and totals from the [order details] table, nothing
> special there.
> However, if I uncomment the 'where ordervalue > 500' line and run it I get
> an error that says "Invalid column name 'ordervalue' "
> Any ideas?
> Thanks,
> td.
>
> select
> o.orderid
> ,o.orderdate
> ,c.companyname
> ,sum(unitprice * quantity) as ordervalue
> from
> orders o
> join
> customers c
> on
> o.customerid = c.customerid
> join
> [order details] od
> on o.orderid = od.orderid
> --where ordervalue > 500
> group by o.orderid, c.companyname, o.orderdate

it is a bit complicated, but to get a useful error message try this instead
when joining you cant use a where on an agregate like that because the
column may not exist, it is based on the results

select
o.orderid
,o.orderdate
,c.companyname
,sum(unitprice * quantity) as ordervalue
from
orders o
join
customers c
on
o.customerid = c.customerid
join
[order details] od
on o.orderid = od.orderid
where sum(unitprice * quantity) > 500
group by o.orderid, c.companyname, o.orderdate

and here is how to make it work;

select
o.orderid
,o.orderdate
,c.companyname
,sum(od.unitprice * od.quantity) as ordervalue
from
orders o
join
customers c
on
o.customerid = c.customerid
join
[order details] od
on o.orderid = od.orderid

group by o.orderid, c.companyname, o.orderdate
having sum(od.unitprice * od.quantity) > 500

|||Thanks a lot, done the trick.

td.

"Lefty" <synergysynergy@.hotmail.com> wrote in message
news:424b6a22$0$57123$c30e37c6@.lon-reader.news.telstra.net...
> "toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
> news:3b0pjhF6d9fg9U1@.individual.net...
>> Hi,
>>
>> This simple code using the Northwind db and SQL 2000...when I have the
>> 2nd
>> from botton line commented out as I do now it works well and give me a
>> summary of the orders and totals from the [order details] table, nothing
>> special there.
>>
>> However, if I uncomment the 'where ordervalue > 500' line and run it I
>> get
>> an error that says "Invalid column name 'ordervalue' "
>>
>> Any ideas?
>>
>> Thanks,
>>
>> td.
>>
>>
>> select
>> o.orderid
>> ,o.orderdate
>> ,c.companyname
>> ,sum(unitprice * quantity) as ordervalue
>> from
>> orders o
>> join
>> customers c
>> on
>> o.customerid = c.customerid
>> join
>> [order details] od
>> on o.orderid = od.orderid
>> --where ordervalue > 500
>> group by o.orderid, c.companyname, o.orderdate
>>
>>
> it is a bit complicated, but to get a useful error message try this
> instead
> when joining you cant use a where on an agregate like that because the
> column may not exist, it is based on the results
> select
> o.orderid
> ,o.orderdate
> ,c.companyname
> ,sum(unitprice * quantity) as ordervalue
> from
> orders o
> join
> customers c
> on
> o.customerid = c.customerid
> join
> [order details] od
> on o.orderid = od.orderid
> where sum(unitprice * quantity) > 500
> group by o.orderid, c.companyname, o.orderdate
>
> and here is how to make it work;
>
> select
> o.orderid
> ,o.orderdate
> ,c.companyname
> ,sum(od.unitprice * od.quantity) as ordervalue
> from
> orders o
> join
> customers c
> on
> o.customerid = c.customerid
> join
> [order details] od
> on o.orderid = od.orderid
> group by o.orderid, c.companyname, o.orderdate
> having sum(od.unitprice * od.quantity) > 500
>
>
>
>
>|||toedipper (send_rubbish_here734@.hotmail.com) writes:
> This simple code using the Northwind db and SQL 2000...when I have the 2nd
> from botton line commented out as I do now it works well and give me a
> summary of the orders and totals from the [order details] table, nothing
> special there.
> However, if I uncomment the 'where ordervalue > 500' line and run it I get
> an error that says "Invalid column name 'ordervalue' "

The only place in the query where you can use an alias is in the ORDER
BY clause.

However, you can use a derived table:

SELECT orderid, orderdate, companyname, ordervalue
FROM (select
o.orderid
,o.orderdate
,c.companyname
,sum(unitprice * quantity) as ordervalue
from
orders o
join
customers c
on
o.customerid = c.customerid
join
[order details] od
on o.orderid = od.orderid
group by o.orderid, c.companyname, o.orderdate) AS x
where ordervalue > 500

Another way is to write the query as:

select
o.orderid
,o.orderdate
,c.companyname
,sum(unitprice * quantity) as ordervalue
from
orders o
join
customers c
on
o.customerid = c.customerid
join
[order details] od
on o.orderid = od.orderid
group by o.orderid, c.companyname, o.orderdate
having SUM(UnitPrice * Quantity) > 500

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp