SQL says wrong column name but it does exist

Question

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)

`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. — Nathan_Sav, Mar 09 '22 at 15:10
@Nathan_Sav it worked. Please answer so I can mark it. Thanks alot — EagerToLearn, Mar 09 '22 at 15:11
Side point: window functions in recursive CTEs behave very strangely anyway so depending on what you are trying to achieve you may want to put it outside the rCTE, see https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15#guidelines-for-defining-and-using-recursive-common-table-expressions — Charlieface, Mar 09 '22 at 15:27
`;with cte as` When you use statement terminators consistently (which you do), you don't need this lazy kludge in front of `with`. It is not a syntax requirement to preface WITH with a semicolon. — SMor, Mar 09 '22 at 16:51

score 0 · Accepted Answer · answered Mar 09 '22 at 15:11

0

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.

answered Mar 09 '22 at 15:11

Nathan_Sav

8,466
2
13
20

SQL says wrong column name but it does exist

1 Answers1