how to access temp table field in subquery?

joost versteegen

hi, I get an error : The multi-part identifier "e.ent_name" could not be bound. when trying to access a field from a temp table in a sub-query.

declare @entities table(ent\_id int, ent\_name varchar(50), startDT datetime)

insert into @entities 
select ent.ent\_id, ent\_name, prod\_job.act\_start\_time\_local
from job as prod\_job
inner join ent as ent on prod\_job.run\_ent\_id = ent.parent\_ent\_id
where prod\_job.wo\_id = 'B.100077779'

select \* from @entities -- so far so good

select \* from @entities as e 
inner join 
(
	select top(1) act\_finish\_time\_local, oper\_id from job as cleaningJob
	where cleaningJob.oper\_id like '%'+ e.ent\_name +'%' ----> error
	order by act\_finish\_time\_local desc
) 
as j on j.act\_finish\_time\_local < e.startDT

how can i fix this? Thanks

Victor Nijegorodov

Have a look at [sql - What is a 'multi-part identifier' and why can't it be bound? - Stack Overflow](https://stackoverflow.com/questions/206558/what-is-a-multi-part-identifier-and-why-cant-it-be-bound/206667)

Mycroft Holmes

Reverse your operation Populate the @Entities table as you are doing change the select query Select from Job inner join on @Entities on - apply the filter here

Never underestimate the power of human stupidity RAH

joost versteegen

thank you, i will try it

Richard Deeming

Try using CROSS APPLY instead: Making OUTER and CROSS APPLY work for you[^]

select * from @entities as e
CROSS APPLY
(
select top(1) act_finish_time_local, oper_id
from job as cleaningJob
where cleaningJob.oper_id like '%'+ e.ent_name +'%'
and cleaningJob.act_finish_time_local < e.startDT
order by act_finish_time_local desc
)
as j

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer