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
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).
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.
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.
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.
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;
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With