Querying a cte - The multi-part identifier could not be bound. How do I fix this so I can query the table? New to SQL accounting background

Question

I am trying to extract a dataset which joins 3 tables

• Employee E
• AbsenceTypes AT
• AbsenceBalance AB

In the data set I need the most recent record from AB.BalanceTime for each AB.EmployeeUID by AB.AbsenceTypesUID.

The data set is correct with the output I need, where it fails is when I query the CTE.

;WITH cte AS
(
    SELECT TOP (1000) 
        AB.[UID],
        AB.BalanceTime,
        AB.AbsenceTypesUID,
        AB.Mins,
        E.FullName,
        E.FirstName, E.LastName,
        AB.EmployeeUID,
        AT.LongName,
        ROW_NUMBER() OVER(PARTITION BY AB.[UID], AB.EmployeeUID ORDER BY AB.BalanceTime DESC) AS RUN
    FROM 
        [RiteqDB].[dbo].[AbsenceBalance] AB
    LEFT JOIN 
        [RiteqDB].[dbo].Employee E ON AB.EmployeeUID = E.UID
    LEFT JOIN 
        [RiteqDB].[dbo].AbsenceTypes AT ON AB.AbsenceTypesUID = AT.UID
)
SELECT * 
FROM cte
WHERE RUN = 1 AND E.FullName = 'john citizen'

Error

Msg 4104, Level 16, State 1, Line 45
The multi-part identifier "E.FullName" could not be bound.

I have googled the problem & from what I understand the joined tables do not interact with the CTE which is why it fails with the condition below.

AND E.FullName = 'john citizen'     

How can I alter the script so I can query the table?

Answer 1

Table alias E is defined inside the CTE only, not in the outer query. In that scope, there is only one (derived) table, that is called cte, and that has all column names that the CTE returns.

In other words, you can just do:

with cte as (...)
select * 
from cte
where run = 1 and fullname = 'john citizen'

If you really want to use aliases, then alias the CTE, and then:

with cte as (...)
select c.* 
from cte c
where c.run = 1 and c.fullname = 'john citizen'