CODEWITHSUNDEEP
sqlssms
ah2Bwise
ah2Bwise

Reputation: 132

why am I getting an 'invalid column' error?

I am getting an 'invalid column' error concerning 'ordervalue'. Using a HAVING statement instead of WHERE doesn't fix it, and removing the '>10000' filter allows the code to run fine.

What am I missing?

Thank you

select
    customers.customerid,
    customers.companyname,
    orderdetails.orderid,
    ordervalue = orderdetails.quantity * OrderDetails.UnitPrice
from
    customers
join
    orders
on
    customers.customerid = orders.customerid
join
    orderdetails
on
    orderdetails.orderid = orders.orderid
where
    orders.orderdate > '20160101' and
    orders.orderdate < '20161231' and
    ordervalue > 10000
order by
    ordervalue desc

Upvotes: 0

Views: 24

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520998

The problem is the WHERE clause, which is attempting to refer to the alias ordervalue. This is not allowed in standard SQL. You should just repeat the expression:

where
    orders.orderdate > '20160101' and
    orders.orderdate < '20161231' and
    orderdetails.quantity * OrderDetails.UnitPrice > 10000

Upvotes: 1

Related Questions