Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Referencing a calculated column in the where clause SQL

This line of code is a snippet from my select statement.

frdFreedays - DateDiff(dd,conReceiptToStock,GetDate()) As FreeDaysRemaining

Below is a snippet from my where clause

and frdFreedays - DateDiff(dd,conReceiptToStock,GetDate()) <= @intFreeDays

The question I have is how can I reference the FreeDaysRemaining column and so I can compare it to @intFreeDays

I am looking for something like this

Freedays <= @intFreeDays
like image 974
tschock Avatar asked Mar 15 '12 13:03

tschock


People also ask

How do you reference a column in SQL?

You use column-ref to reference a column (database field), optionally qualified by a table and/or schema name. The column reference comprises the data type of the database field that the column represents (see Data Types).

Can you reference a created column in SQL?

You can only reference a calculated column in the order by clause. For any other use either use a sub-query or repeat the logic.

Can you reference alias in WHERE clause?

5 Answers. Show activity on this post. You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated.

Can we use SQL function in WHERE clause?

SQL Server offers many handy functions that can be used either in your SELECT clause or in your WHERE clause. For the most part these functions provide complex coding that would be very difficult to get this same functionality without these functions.


2 Answers

You can't reference an alias anywhere except ORDER BY. One workaround (aside from the obvious possibility of repeating the expression) is to put it in a derived table:

SELECT FreeDaysRemaining --, other columns
FROM
(
  SELECT frdFreedays - DATEDIFF(DAY, conReceiptToStock, GETDATE()) AS FreeDaysRemaining
    --, other columns
  FROM ...
) AS x
WHERE FreeDaysRemaining <= @intFreeDays;
like image 81
Aaron Bertrand Avatar answered Oct 15 '22 21:10

Aaron Bertrand


In addition to Aaron's answer, you could use a common table expression:

;with cte_FreeDaysRemaining as
    (
        select
            frdFreedays - DateDiff(dd,conReceiptToStock,GetDate()) As FreeDaysRemaining
            --, more columns
        from yourtable
    )
    select
        FreeDaysRemaining
        --, more columns
    from cte_FreeDaysRemaining
    where FreeDaysRemaining <= @intFreeDays
like image 24
Joey Avatar answered Oct 15 '22 22:10

Joey