Using Alias column in 'WHERE' clause SQL Server

Question

Hi I'm trying to write a SQL statement and having issues using the 'ordercost' column in my WHERE clause.

The result set should produce 11 rows with dates between 'Jan 1 1992' AND 'Mar 30 1992' and also cost more than $1,500. With the code below I'm getting an error about 'column not valid'.

I suspect I may need to use a subquery or could be way off but after searching for the last 3 hours I'm unsure how to complete this.

Anyone have any suggestions on how to format this?

select  orders.orderid, 
            products.productname, 
            customers.CompanyName,
            orderdate = CONVERT(char(11), orders.orderdate, 100),
            newshippeddate = CONVERT(char(11), orders.shippeddate + 10 , 100),
            ordercost = (OrderDetails.Quantity * Products.UnitPrice)
    from orders`enter code here`
    INNER JOIN orderdetails ON orders.orderid = orderdetails.orderid
    INNER JOIN products ON orderdetails.productid = products.productid
    INNER JOIN customers ON orders.customerid = customers.customerid
    where orders.orderdate BETWEEN 'Jan 1 1992' AND 'Mar 30 1992' 
      AND ordercost >= 1500.00

Given the typical relationship between orders and details, I'll bet that you need to sum the expression you use for ordercost. Which makes this a very different problem. If you don't sum, you get a value that is specific to a detail row but your resultset includes no useful information about the "item" found in the detail row. — SMor, Mar 23 '18 at 20:13
You can use a CTE (Common Table Expression) in order to use column aliases in WHERE clauses. It's usually a very minor refactoring, and it's almost always pure syntactic sugar and doesn't affect performance or plan choices. — pmbAustin, Mar 23 '18 at 20:14
MySQL lets you reference expressions this way but it's not standard SQL. Maybe that's some of your confusion. — shawnt00, Mar 23 '18 at 20:25

score 1 · Accepted Answer · answered Mar 23 '18 at 20:00

I suspect SQL Server complains on the ordercost dynamic column. If yes, then try this:

select  
    orders.orderid, 
    products.productname, 
    customers.CompanyName,
    orderdate = CONVERT(char(11), orders.orderdate, 100),
    newshippeddate = CONVERT(char(11), orders.shippeddate + 10 , 100),
    ordercost = (OrderDetails.Quantity * Products.UnitPrice)
from orders
    INNER JOIN orderdetails ON orders.orderid = orderdetails.orderid
    INNER JOIN products ON orderdetails.productid = products.productid
    INNER JOIN customers ON orders.customerid = customers.customerid
where (orders.orderdate BETWEEN 'Jan 1 1992' AND 'Mar 30 1992') 
        AND (OrderDetails.Quantity * Products.UnitPrice) >= 1500.0

Since ordercost is not a real column, I have moved it's expression into WHERE. However, this will make WHERE condition not SARGable.

Thank you @andrews worked perfectly! Yes, SQL Server was complaining 'ordercost' was not a real column due it being created as an alias in the 'select' statement. What do mean by "not SARGable" — Chris Kavanagh, Mar 23 '18 at 20:10
@ChrisKavanagh not SARGable means the index will not be used and the condition will have to be evaluated every time. Read more here: https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable . If the answer was helpful would appreciate if you accept it. Hit the check mark to the left under the down arrow. Thanks. — andrews, Mar 23 '18 at 20:13

score 1 · Answer 2 · answered Mar 23 '18 at 20:04

You can't use a column alias (ordercost is an alias) until the ORDER BY CLAUSE.

There's another way though:

select  orders.orderid, 
        products.productname, 
        customers.CompanyName,
        computed.orderdate,
        computed.newshippeddate,
        computed.ordercost

    from orders`enter code here`
    INNER JOIN orderdetails ON orders.orderid = orderdetails.orderid
    INNER JOIN products ON orderdetails.productid = products.productid
    INNER JOIN customers ON orders.customerid = customers.customerid
    CROSS APPLY (
    SELECT 
            orderdate = CONVERT(char(11), orders.orderdate, 100),
            newshippeddate = CONVERT(char(11), orders.shippeddate + 10 , 100),
            ordercost = (OrderDetails.Quantity * Products.UnitPrice)
    ) computed
    where (computed.orderdate BETWEEN 'Jan 1 1992' AND 'Mar 30 1992') AND computed.ordercost >= 1500.00

I can't test this since I don't have your table but it parses OK. The idea is to use CROSS APPLY as an expression evaluator. Then you can use your aliases where you want them.

score 0 · Answer 3 · answered Mar 23 '18 at 20:08

I'd probably use @andrews solution in this case. But, aliases in the SELECT clause can only be referenced in the ORDER BY clause. If you truly want OrderCost to be a reference-able column, you can use an outer apply to derive it. This can be handy when you have complicated calculations, that may eventually need to have additional calculations done on them later. This prevents you from having to retype that formula each time.

Edit: my apologies user1443098, I was writing this at the same time as you, looks like.

select  orders.orderid, 
        products.productname, 
        customers.CompanyName,
        orderdate = CONVERT(char(11), orders.orderdate, 100),
        newshippeddate = CONVERT(char(11), orders.shippeddate + 10 , 100),
        ordercost = derived.ordercost
from orders
INNER JOIN orderdetails ON orders.orderid = orderdetails.orderid
INNER JOIN products ON orderdetails.productid = products.productid
INNER JOIN customers ON orders.customerid = customers.customerid
OUTER APPLY(SELECT ordercost = OrderDetails.Quantity * Products.UnitPrice) derived
WHERE (orders.orderdate BETWEEN 'Jan 1 1992' AND 'Mar 30 1992') AND derived.ordercost >= 1500.00

score 0 · Answer 4 · answered Mar 23 '18 at 20:18

You can use a CTE (Common Table Expression) in order to use column aliases in WHERE clauses. It's usually a very minor refactoring, and it's almost always pure syntactic sugar and doesn't affect performance or plan choices.

For example:

WITH OrderProductCustomers AS (
    select orders.orderid, 
           products.productname, 
           customers.CompanyName,
           CONVERT(char(11), orders.orderdate, 100) AS orderdate,
           CONVERT(char(11), orders.shippeddate + 10 , 100) AS newshippeddate,
           (OrderDetails.Quantity * Products.UnitPrice) AS ordercost
      from orders
           INNER JOIN orderdetails ON orders.orderid = orderdetails.orderid
           INNER JOIN products ON orderdetails.productid = products.productid
           INNER JOIN customers ON orders.customerid = customers.customerid
)
SELECT orderid, productname, CompanyName, orderdate, newshippeddate, ordercost
  FROM OrderProductCustomers
 WHERE orderdate BETWEEN 'Jan 1 1992' and 'March 30 1992'
   AND ordercost >= 1500.00

Using Alias column in 'WHERE' clause SQL Server

4 Answers4