SQL says wrong column name but it does exist

I have this query

declare @department_code varchar(8), @basedate varchar(8);
set @department_code = 'A';
set @basedate = '20200501';

Create table #DEPARTMENT_MT(DEPARTMENT_CODE varchar(8), REVISION_DATE varchar(8), PARENT_DEPARTMENT_CODE varchar(8), DEL_FLG bit);

insert into #DEPARTMENT_MT values('A', '20200101',  'X', 0);
insert into #DEPARTMENT_MT values('A', '20220101',  '', 0);

insert into #DEPARTMENT_MT values('B', '20200101', 'A', 0);
insert into #DEPARTMENT_MT values('B', '20220101', '', 0);

insert into #DEPARTMENT_MT values('C', '20200101', 'B', 0);
insert into #DEPARTMENT_MT values('C', '20220101', 'A', 0);

insert into #DEPARTMENT_MT values('D', '20200101', 'C', 0);
insert into #DEPARTMENT_MT values('D', '20220101', 'F', 0);


insert into #DEPARTMENT_MT values('E', '20200101', 'D', 0);
insert into #DEPARTMENT_MT values('F', '20200101', 'E', 0);
insert into #DEPARTMENT_MT values('G', '20200101', 'F', 0);
insert into #DEPARTMENT_MT values('H', '20200101', 'G', 0);

;with cte as 
(
    select *, cast(0 as bigint) as seqnum   from #DEPARTMENT_MT where DEPARTMENT_CODE=@department_code and REVISION_DATE = (select max(REVISION_DATE) from #DEPARTMENT_MT where REVISION_DATE < @basedate and DEPARTMENT_CODE=@department_code)
    union all
    select t.*, row_number() over (partition by t.DEPARTMENT_CODE, t.REVISION_DATE order by t.REVISION_DATE desc) as seqnum1
        from  #DEPARTMENT_MT t
        inner join cte on cte.DEPARTMENT_CODE = t.PARENT_DEPARTMENT_CODE AND t.REVISION_DATE <= cte.REVISION_DATE 
        where seqnum1 = 1
)

select * from cte;

What I want to do is to get only the row with have row_number = 1 in the recursive part

select t.*, row_number() over (partition by t.DEPARTMENT_CODE, t.REVISION_DATE order by t.REVISION_DATE desc) as seqnum1
    from  #DEPARTMENT_MT t
    inner join cte on cte.DEPARTMENT_CODE = t.PARENT_DEPARTMENT_CODE AND t.REVISION_DATE <= cte.REVISION_DATE 
    where seqnum1 = 1

And I’m getting this error:

Invalid column name ‘seqnum1’.

If I change the column’s name from seqnum1 to seqnum, it won’t generate an error, but the result is not as I expected

Why does using seqnum1 in the where condition results in the above error? (If I don’t include the where condition, there will be no error)

>Solution :

seqnum1 doesn’t exist in #DEPARTMENT_MT it will do if you wrap it so select * from ( your query ) as q where q.seqnum1=1 for example.