Invalid Column Name After Adding Where Clause

Question

I am trying to run a query and it works fine when running the below query:

SELECT 'Delivery' activity_type,
left(pod.ID1, charindex('*', pod.ID1) - 1) load_number,
DATEADD(hour, CONVERT(INT, LEFT(CONVERT(VARCHAR(2), ISNULL(Dock_In_Time, 
'0')), 2)), CONVERT(DATETIME, ID2)) expected_arrival
FROM [Server].[Database].[dbo].[POD_Sched_Del] pod
--WHERE expected_arrival between '6/7/18' and '6/8/18'
ORDER BY expected_arrival asc, activity_type

When I add in the following line of code that is in the comment:

--WHERE expected_arrival between '6/7/18' and '6/8/18'

I get these error messages:

Msg 207, Level 16, State 3, Line 37 Invalid column name 'expected_arrival'.
Msg 207, Level 16, State 3, Line 37 Invalid column name 'expected_arrival'.

[Logical processing order of the SELECT statement](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-2017#logical-processing-order-of-the-select-statement) — Damien_The_Unbeliever, Jun 08 '18 at 13:35
[Referring to a Column Alias in a WHERE Clause](https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause) — Alex K., Jun 08 '18 at 13:36
in the WHERE clause you can't use alias but the real field. So for you: DATEADD(hour, CONVERT(INT, LEFT(CONVERT(VARCHAR(2), ISNULL(Dock_In_Time, '0')), 2)), CONVERT(DATETIME, ID2)) between... — Joe Taras, Jun 08 '18 at 13:39

score 4 · Accepted Answer · answered Jun 08 '18 at 13:37

Wrap the original query up as a derived table (sub-query), then you can have that WHERE clause condition:

select *
from
(
    SELECT 'Delivery' activity_type,
    left(pod.ID1, charindex('*', pod.ID1) - 1) load_number,
    DATEADD(hour, CONVERT(INT, LEFT(CONVERT(VARCHAR(2), ISNULL(Dock_In_Time, 
    '0')), 2)), CONVERT(DATETIME, ID2)) expected_arrival
    FROM [Server].[Database].[dbo].[POD_Sched_Del] pod
) dt
WHERE expected_arrival between '6/7/18' and '6/8/18'
ORDER BY expected_arrival asc, activity_type

Brad · Answer 2 · 2018-06-08T13:38:39.827

1

You can not use an alias you are using in a where clause, that column does not exist in the table.

You can change your where clause to this which is the column you are trying to alias:

    WHERE DATEADD(hour, CONVERT(INT, LEFT(CONVERT(VARCHAR(2), ISNULL(Dock_In_Time, 
'0')), 2)), CONVERT(DATETIME, ID2)) between '6/7/18' and '6/8/18'

edited Jun 08 '18 at 13:38

answered Jun 08 '18 at 13:35

Brad

3,454
3
27
50

The whole expression starting `DATEADD(hour,...` is what becomes `expected_arrival`. It would seem to me that adding or removing some number of hours to a date could change which day it falls within and so the filter may or may not be correct if you're just working with `ID2`. – Damien_The_Unbeliever Jun 08 '18 at 13:37
1

Sorry missed the entire calculation, I updated my solution but since there is so much being done, this would not be the best route, the other solution with the sub select would work better with all the functions being done on the values in the where clause. – Brad Jun 08 '18 at 13:39

Invalid Column Name After Adding Where Clause

2 Answers2