Why am I getting "Invalid column name" error when the column exists

Question

This is my code:

declare @maxsnap table (sita varchar(10), date date, SNAPSHOT_DATE date)

insert into @maxsnap 
   select 
       sita, date, max(SNAPSHOT_DATE) snapshot 
   from 
       [UKRMC].[dbo].[Roll_forecast] 
   where 
       date between '2018-03-21' and '2018-05-31'
   group by 
       sita, date 

select 
    roll.DATE, roll.SITA,
    contacts.rooms,
    roll.SEGMENT, roll.RNS 
from 
    [UKRMC].[dbo].[Roll_forecast] roll
join 
    [UKRMC].[dbo].[Contacts] contacts on contacts.SITA = roll.SITA
join 
    @maxsnap snap on roll.DATE = snap.date 
                  and roll.SITA = snap.sita 
                  and roll.SNAPSHOT_DATE = snap.snapshot
where 
    roll.date between '2018-03-21' and '2018-05-31'

The error I am getting is

Invalid column name 'snapshot'

when I join the @maxnsap table variable. But that column does exist!

did you mean `max(SNAPSHOT_DATE) AS snapshot` in your `SELECT`? — Forty3, Mar 21 '18 at 17:27
@Forty3 I've tried that and it still gives me the same error — Sorath, Mar 21 '18 at 17:27
@maxsnap doesn't have the column `snapshot` so this doesn't work `roll.SNAPSHOT_DATE = snap.snapshot` — WhatsThePoint, Mar 21 '18 at 17:28
Sorry - you define the table `@maxsnap` with a column named `SNAPSHOT_DATE` not `snapshot`. So in your final `SELECT` you need to refer to `snap.SNAPSHOT_DATE` — Forty3, Mar 21 '18 at 17:28

score 1 · Answer 1 · answered Mar 21 '18 at 17:29

1

You have this condition:

roll.DATE = snap.date

However, the column is called snapshot_date:

roll.DATE = snap.snapshot_date

answered Mar 21 '18 at 17:29

Gordon Linoff

1,242,037
58
646
786

score 1 · Accepted Answer · answered Mar 21 '18 at 17:30

1

Error in join

     and roll.SNAPSHOT_DATE = snap.SNAPSHOT_DATE

answered Mar 21 '18 at 17:30

Mazhar

3,797
1
12
29

Thank you, this worked! However, I called it 'snapshot' so it doesn't make sense to me why I need to refer to the original column name – Sorath Mar 21 '18 at 17:36
1

The column in the @maxsnap table is called SNAPSHOT_DATE. In your query to populate the table you've used the alias snapshot but this doesn't change the name of the column in the table – Mazhar Mar 21 '18 at 17:40
@Sorath you cannot use the alias name as to the compiler the alias hasn't been assigned yet, if you see [this answer](https://stackoverflow.com/a/3841804/7147233) it explains that each section of the select statement in ran in an order of precedence, stating that the from is done before the select – WhatsThePoint Mar 21 '18 at 17:42
That alias only existed for the initial `insert` and even there it was ignored. Since you didn't specify a column list it just assumed the columns in the same order as you created the table. – shawnt00 Mar 21 '18 at 17:47

Why am I getting "Invalid column name" error when the column exists

2 Answers2