Using a temp table in a view
I would really like to create a view.
I know you can't use temp tables in MSSQL2005 views. Without rewriting the sql, is there anything obvious I've missed?
Backup plan is to use a stored proc.
Cheers
select * into #temp from vwIncidents
SELECT vwIncidents.incidentcod开发者_C百科e, employeecode, EMPOS.POS_L4_CDA as areaAtTimeOfIncident
into #temp1
FROM vwIncidents
INNER JOIN EMPOS ON vwIncidents.employeecode = EMPOS.DET_NUMBERA
WHERE EMPOS.POS_STARTC < vwIncidents.incidentdate
AND (EMPOS.POS_ENDD > vwIncidents.incidentdate OR EMPOS.POS_ENDD IS NULL)
order by incidentcode
select #temp.*, #temp1.areaAtTimeOfIncident from #temp
left outer join #temp1 on #temp.incidentcode = #temp1.incidentcode
and #temp.employeecode = #temp1.employeecode
order by incidentcode
You could use a CTE:
WITH cteIncidents (incidentcode, employeecode, areaAtTimeOfIncident)
AS
(
SELECT
vwIncidents.incidentcode, employeecode, EMPOS.POS_L4_CDA as areaAtTimeOfIncident
FROM
vwIncidents
INNER JOIN EMPOS ON vwIncidents.employeecode = EMPOS.DET_NUMBERA
WHERE EMPOS.POS_STARTC < vwIncidents.incidentdate
AND (EMPOS.POS_ENDD > vwIncidents.incidentdate OR EMPOS.POS_ENDD IS NULL)
)
SELECT
incidentcode, employeecode, areaAtTimeOfIncident
FROM
cteIncidents
left outer join vwIncidents on vwIncidents.incidentcode = cteIncidents.incidentcode
and vwIncidents.employeecode = cteIncidents.employeecode
ORDER BY
incidentcode
(Might need to change the join to a right, but you get the idea...)
Hav you tried rewriting this without the use of temp tables?
Something like
select temp.*,
temp1.areaAtTimeOfIncident
from (
select *
from vwIncidents
) temp left outer join
(
SELECT vwIncidents.incidentcode,
employeecode,
EMPOS.POS_L4_CDA as areaAtTimeOfIncident
FROM vwIncidents INNER JOIN
EMPOS ON vwIncidents.employeecode = EMPOS.DET_NUMBERA
WHERE EMPOS.POS_STARTC < vwIncidents.incidentdate
AND (
EMPOS.POS_ENDD > vwIncidents.incidentdate
OR EMPOS.POS_ENDD IS NULL
)
) temp1 on temp.incidentcode = temp1.incidentcode
and temp.employeecode = temp1.employeecode
order by incidentcode
Use the WITH
statement.
精彩评论