Creating View with Temporary table

[gtb12314]

Hi All,

In my SQL I am having temporary tables. And in Microsoft SQL Server Management Studio (Microsoft SQL Server 2005) whenever I execute sql statement its working fine & I am getting the records.

My SQL statement is using 2 databases as follows:
1. PerformanceDeficiencyNotice
2. HRDataWarehouse

Both the above databases are SQL SERVER 2000(80) with a compatibility level of 80.

The problem is when I am trying to create a new view with my sql statement and when I am saying “Verify SQL Syntax”, I am getting an error as “Invalid Object Name ‘#pdninfo’.

And when I am saying “execute SQL”, I am getting an error as “Unable to parse query text” but when I am continuing with the error, the sql statement is running and I am getting the data.

And now when I am trying to save the view I am getting the error as below
“Incorrect syntax near the keyword ‘INTO’”.
Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.

Please suggest how to solve this problem. Any help is greatly appreciated.

Thank You

My SQL statement is as follows:

SELECT
pdn.transactionid,
pdn.employeenbr,
pdn.lastname,
pdn.firstname,
pdn.processlevel,
pl.facilityname as processlevelname,
pdn.department,
pdn.jobcode,
pdn.title,
pdn.supemployeenbr,
pdn.managername,
pdn.timeframe as pdn_timeframe,
pdn.actualeffectivedate as pdn_startdate,
/*actualeffectivedate is the start date for the pdn.  starteddate is when info starts being put in the system*/
/*the pdn end date has to be calculated for the pdn based on the timeframe and actualeffectivedate*/
case when pdn.actualeffectivedate <> convert(datetime,'01/01/1900',110) then
	case pdn.timeframe
		when '30' then dateadd(month,1,pdn.actualeffectivedate)
		when '60' then dateadd(month,2,pdn.actualeffectivedate)
		when '90' then dateadd(month,3,pdn.actualeffectivedate)
		else null
	end
end as pdn_enddate,
pdn.status as pdn_status,
status.description as pdn_statusdesc,
pdn.managersignoff as pdn_managersignoff,
pdn.managersignoffdate as pdn_managersignoffdate,
pdn.associatesignoff as pdn_associatesignoff,
pdn.associatesignoffdate as pdn_associatesignoffdate,
pdn.witnessname as pdn_witnessname,
/*the start date for the extension has to be calculated by subtracting 30 days from the evaluationdate*/
/*	where the evaluationtype = 'X' (Extension Final).*/
/*there is only one timeframe of 30 days for an extension and only one extension is allowed per pdn for an associate*/
case 
	when (eval.evaluationtype = 'X' and eval.status not in ('C','D','N')) then dateadd(month,-1,eval.evaluationdate)
	else null
end as ext_startdate,
eval.evaluationdate as eval_evaluationdate,/*end date of the evaluation or extension*/
eval.evaluationtype as eval_evaluationtype,
evaltype.description as eval_evaltypedesc,
eval.status as eval_status,
status2.description as eval_statusdesc,
eval.effectivedate as eval_effectivedate,
eval.managersignoff as eval_managersignoff,
eval.managersignoffdate as eval_managersignoffdate,
eval.associatesignoff as eval_associatesignoff,
eval.associatesignoffdate as eval_associatesignoffdate,
eval.witnessname as eval_witnessname
into #pdninfo
FROM [PerformanceDeficiencyNotice].[dbo].[PDNMain] pdn
left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationsMain] eval
on pdn.transactionid = eval.transactionid
left outer join [HRDataWarehouse].[dbo].[ProcessLevel] pl
on pdn.processlevel = pl.processlevel
left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status
on pdn.status = status.status and status.type = 'PDN'
left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status2
on eval.status = status2.status and status2.type = 'EVAL'
left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationTypes] evaltype
on eval.evaluationtype = evaltype.type
/*select active pdns from PDNMain (status:  'A' = Approved, 'S' = Submitted)*/
WHERE pdn.status in ('A','S')
/*select extensions from EvaluationsMain (evaluation type:  'X' = Extension Final; status: <> 'C' - Completed,*/
/*	'D' - In Progress, or 'N' - Not started)*/
OR (eval.evaluationtype = 'X' and eval.status not in ('C','D','N'))

/*get last performance rating and last (maximum) performance review date from PerformanceReviewHistory*/
/*Note:  A PerformanceReviewHistory record gets created within a couple of days after an associate is hired.*/
/*	     The rating and updatedate are null initially.  Aggregate functions (i.e. MAX) ignore null values.*/
/*       You must check for "updatedate IS NOT NULL" as shown below or the record will be dropped.*/
SELECT distinct(#pdninfo.employeenbr), perfreview.rating, perfreview.updatedate
into #perfreview
FROM #pdninfo, [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview
WHERE #pdninfo.employeenbr = perfreview.employeenbr 
AND perfreview.updatedate = 
	(SELECT max(updatedate) 
     FROM [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview2
     WHERE perfreview2.employeenbr = perfreview.employeenbr
     AND updatedate IS NOT NULL)

/*select active pdns ('orig' = original)*/
SELECT 'orig' as orig_or_ext,
#pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate,
/*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/
emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi,
(SELECT emp2.lastname
 FROM [HRDataWarehouse].[dbo].[Employee] emp2
 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname,
(SELECT emp2.firstname
 FROM [HRDataWarehouse].[dbo].[Employee] emp2
 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname,
(SELECT emp2.mi
 FROM [HRDataWarehouse].[dbo].[Employee] emp2
 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi
FROM #pdninfo
left outer join #perfreview
on #pdninfo.employeenbr = #perfreview.employeenbr
left outer join [HRDataWarehouse].[dbo].[Employee] emp
on #pdninfo.employeenbr = emp.employeenbr
WHERE #pdninfo.pdn_status in ('A','S')

union

/*select extensions ('ext' = extension)*/
SELECT
'ext' as orig_or_ext,
#pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate,
/*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/
emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi,
(SELECT emp2.lastname
 FROM [HRDataWarehouse].[dbo].[Employee] emp2
 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname,
(SELECT emp2.firstname
 FROM [HRDataWarehouse].[dbo].[Employee] emp2
 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname,
(SELECT emp2.mi
 FROM [HRDataWarehouse].[dbo].[Employee] emp2
 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi
FROM #pdninfo
left outer join #perfreview
on #pdninfo.employeenbr = #perfreview.employeenbr
left outer join [HRDataWarehouse].[dbo].[Employee] emp
on #pdninfo.employeenbr = emp.employeenbr
WHERE #pdninfo.eval_evaluationtype = 'X' and #pdninfo.eval_status not in ('C','D','N')

drop table #pdninfo
drop table #perfreview

 

[genomon]

Wow - pretty daunting code, gtb. Why don't you just create a real table to avoid all the errors?

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[memdiscount]

The answer is already in the question:

Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.

So, you should not use temp table in create view. Use fixed table as above said.

Thanks