Trying to Use a Computed/Generated Field as Link to Another Table-CodePudding

I need to pull one table as left join or outer apply using a field that is generated. The right table has a field, pnlNumber, which is a concat of 2 different fields in the left table. So, I first have to put together the pnlNumber using the DeptCode and EmpNumber from the left table. This field is then used to link to the Personnel table I have tried using a cte with a left join and also using an outer apply. In both cases the generated pnlNumber is an invalid field. Any suggestions for another way to go?

The relevant table info looks as such:

Employee Table (Left Table): 
    DeptCode int,
    EmpNumber int

Personnel Table (Right Table):
    pnlNumber nvarchar(7),
    Area nvarchar(25)

Here is the sql for both attempts:

with cte as(
    select right( '00'   rtrim( convert(char(2), emp.DeptCode) ), 2 )  
           right( '00000'   rtrim( convert(char(5), emp.EmpNumber) ), 5 ) as pnlNumber
    from RDB..Employees as emp
)
select cte.pnlNumber, pnl.Area
from cte
left join
THRDB..Personnel as pnl
on cte.pnlNumber = pnl.pnlNumber





select  RIGHT(('00'   RTRIM(CONVERT(CHAR(2), emp.DeptCode))), 2)
          RIGHT(('00000'   RTRIM(CONVERT(CHAR(5), emp.EmpNumber))), 5) AS pnlNumber,
        comb.Area
from RDB..Employees as emp
outer apply(
    select pnlNumber, Area
    from THRDB..Personnel
) as comb
where comb.pnlNumber = emp.pnlNumber

CodePudding user response：

It appears you're not actually selecting columns from the Employees table therefore you should be able to select from Personnel with an exists criteria:

select pnlNumber, Area
from THRDB.dbo.Personnel p
where exists (
  select * from RDB.dbo.Employees e
  where Right(('00'   RTrim(Convert(char(2), emp.DeptCode))), 2)
         Right(('00000'   RTrim(Convert(char(5), emp.EmpNumber))), 5)
       = p.pnlNumber
);

If you did want to select columns from both tables you could use apply(values(<your computed column>)) to materialise the Employees.pnlNumber, such as:

select e.<columns>, p.<columns>
from RDB.dbo.Employees e
cross apply(
  values(Right(('00'   RTrim(Convert(char(2), e.DeptCode))), 2) 
           Right(('00000'   RTrim(Convert(char(5), e.EmpNumber))), 5))
)v(pnlNumber)
left join THRDB.dbo.Personnel p on p.pnlNumber = v.pnlNumber;