I have a query that should be implemented in an MSSQL View so no local variables allowed, this query is working but it has to call a function to return a temporary table, this function is called inside a subquery, inside a left join, and I get the multi-part identifier …. can not be bound error. The query looks like this:
Select a, b, c,...., z From tablea left join (select * from fnGetTempTable (tablea.pkid) t1 on tablea.id = t1.id left join ....etc
This temporary table returned by the function requires the parameter that tablea has, but I can’t get it to that level, any suggestions please?
You can use
Note, however, that in an apply, the entire inner
select is logically evaluated per row. So the results may be different from what you’d expect from a join, where rows are matched based on the
on clause. (Obviously the compiler may choose other strategies, the effect will be the same.)
There is no
on clause, instead use a
where inside the apply.
cross apply to simulate an inner join, use
outer apply for a left join
Select a, b, c,...., z From tablea outer apply ( select * from fnGetTempTable (tablea.pkid) t1 where tablea.id = t1.id ) t1 left join ....etc