Using a Temporary Table in a View in Order to Combine three Queries

Question

Post reply

Using a Temporary Table in a View in Order to Combine three Queries

Rick44

Mr or Mrs. 500

Points: 564
More actions
July 23, 2013 at 3:45 pm

#296842

Hi all. I am trying to create a view using the three queries below and I get the error message Views or functions are not allowed on temporary tables. Is there a way to do that or is there a way to combine the three queries below so I don't have to use a temp table so I create a view?
Thanks!
--Query 1
SELECT * INTO #MOVEMENTS
FROM [GW_DW].[dbo].[DimStatusHistory] d
WHERE TransferFromToProgram<>''
AND d.Status=12
;
--Query 2
SELECT DISTINCT
n.[CLT_NBR]
,n.[CHILD_NAME]
,n.[ReasonKey]
,n.[ReasonDesc]
,n.[EFFECT_DT]
,n.[Status]
,n.[STATUS_DESC]
,n.[DESCRIPT]
,n.[TRAN_TYPE]
,n.[OTYPE]
,n.[Old_FID]
,n.[NTYPE]
,n.[New_FID]
,n.[TransferFromToProgram]
,[ECMS].dbo.[FN_PRIOR_EFFECT_DT_FOR_STATUS](n.[EFFECT_DT],n.[CLT_NBR],12)
AS PRIOR_EFFECT_DT
,[ECMS].dbo.[FN_NEXT_EFFECT_DT_FOR_STATUS](n.[EFFECT_DT],n.[CLT_NBR],12)
AS FUTURE_EFFECT_DT
,n.[TOTAL_DAYS]
INTO #NEW_MOVEMENTS
FROM [GW_DW].[dbo].[DimStatusHistory] n
LEFT OUTER JOIN #MOVEMENTS m ON n.CLT_NBR=m.CLT_NBR
WHERE n.TransferFromToProgram NOT IN ('','FBH - TFBH','TFBH - FBH')
AND n.EFFECT_DT BETWEEN @from_dt AND @to_dt
AND n.COUNTY='NYC' AND n.OTYPE NOT IN ('RTC', 'SLIP')
AND n.NTYPE NOT IN ('RTC', 'SLIP')
AND n.Status=12
;
--Query 3
SELECT n.[CLT_NBR]
,n.[CHILD_NAME]
,c.CIN
,cl.DOB
,c.Age
,c.Sex
,c.PlcSource
,w.PLACED_DT AS APD
,w.IPD
,n.[ReasonKey]
,n.[ReasonDesc]
,n.[EFFECT_DT]
,n.[TransferFromToProgram]
,n.[OTYPE]
,n.[Old_FID]
,h1.Rsrc_Name AS Old_FP_Name
,h1.Orig_Cert AS Old_FP_Orig_Cert
,n.[NTYPE]
,n.[New_FID]
,h2.Rsrc_Name AS New_FP_Name
,h2.Orig_Cert AS New_FP_Orig_Cert
,n.PRIOR_EFFECT_DT
,m.Old_FID AS PRIOR_Old_FID
,n.FUTURE_EFFECT_DT
,f.New_FID AS FUTURE_New_FID
,c.STF_NBR
,c.Planner
,s.UNIT
,s.SupervisorName
,s.[SITE]
,s.DirectorName
,CASE WHEN (n.NTYPE='KINS' AND n.OTYPE <> 'KINS') THEN 1
WHEN n.ReasonKey IN ('TE','TJ') THEN 1
WHEN ((n.New_FID=m.Old_FID) AND (n.EFFECT_DT<DATEADD(D,22,n.PRIOR_EFFECT_DT))) THEN 1
ELSE 0
END AS POS_SCORE
,CASE WHEN (n.NTYPE='KINS' AND n.OTYPE <> 'KINS') THEN 0
WHEN n.ReasonKey IN ('TE','TJ') THEN 0
WHEN n.New_FID=m.Old_FID AND n.EFFECT_DT<DATEADD(D,22,n.PRIOR_EFFECT_DT)THEN 0
WHEN ((n.Old_FID=f.New_FID) AND (DATEADD(D,22,n.EFFECT_DT)>n.FUTURE_EFFECT_DT)) THEN 0
ELSE -1
END AS NEG_SCORE
FROM #NEW_MOVEMENTS n
LEFT OUTER JOIN #MOVEMENTS m
ON n.CLT_NBR=m.CLT_NBR
AND n.PRIOR_EFFECT_DT=m.EFFECT_DT
LEFT OUTER JOIN #MOVEMENTS f
ON n.CLT_NBR=f.CLT_NBR
AND n.FUTURE_EFFECT_DT=f.EFFECT_DT
LEFT OUTER JOIN GW_DW.dbo.DimClient c
ON c.CLT_NBR=n.CLT_NBR
LEFT OUTER JOIN [ECMS].[dbo].[WFR_CLIENT] w
ON w.CLT_NBR=n.CLT_NBR
LEFT OUTER JOIN [ECMS].[dbo].[CLIENT] cl
ON cl.CLT_NBR=n.CLT_NBR
LEFT OUTER JOIN GW_DW.dbo.DimStaff s
ON s.ECMS_Wrkr_ID=c.STF_NBR
LEFT OUTER JOIN [GW_DW].[dbo].[DimHome_FHD] h1
ON h1.Facility_ID=n.Old_FID
LEFT OUTER JOIN [GW_DW].[dbo].[DimHome_FHD] h2
ON h2.Facility_ID=n.New_FID
WHERE s.[SITE]<>'CGS'
ORDER BY n.CHILD_NAME,n.EFFECT_DT DESC

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You could use CTEs instead of the temporary tables. However, you should check for any possible performance issues.

WITH MOVEMENTS AS(

SELECT *

FROM [GW_DW].[dbo].[DimStatusHistory] d

WHERE TransferFromToProgram <> ''

AND d.STATUS = 12

),

NEW_MOVEMENTS AS(

SELECT DISTINCT n.[CLT_NBR]

,n.[CHILD_NAME]

,n.[ReasonKey]

,n.[ReasonDesc]

,n.[EFFECT_DT]

,n.[Status]

,n.[STATUS_DESC]

,n.[DESCRIPT]

,n.[TRAN_TYPE]

,n.[OTYPE]

,n.[Old_FID]

,n.[NTYPE]

,n.[New_FID]

,n.[TransferFromToProgram]

,[ECMS].dbo.[FN_PRIOR_EFFECT_DT_FOR_STATUS](n.[EFFECT_DT], n.[CLT_NBR], 12) AS PRIOR_EFFECT_DT

,[ECMS].dbo.[FN_NEXT_EFFECT_DT_FOR_STATUS](n.[EFFECT_DT], n.[CLT_NBR], 12) AS FUTURE_EFFECT_DT

,n.[TOTAL_DAYS]

FROM [GW_DW].[dbo].[DimStatusHistory] n

LEFT OUTER JOIN MOVEMENTS m ON n.CLT_NBR = m.CLT_NBR

WHERE n.TransferFromToProgram NOT IN (

''

,'FBH - TFBH'

,'TFBH - FBH'

)

AND n.EFFECT_DT BETWEEN @from_dt AND @to_dt

AND n.COUNTY = 'NYC'

AND n.OTYPE NOT IN (

'RTC'

,'SLIP'

)

AND n.NTYPE NOT IN (

'RTC'

,'SLIP'

)

AND n.STATUS = 12

)

--Query 3

SELECT n.[CLT_NBR]

,n.[CHILD_NAME]

,c.CIN

,cl.DOB

,c.Age

,c.Sex

,c.PlcSource

,w.PLACED_DT AS APD

,w.IPD

,n.[ReasonKey]

,n.[ReasonDesc]

,n.[EFFECT_DT]

,n.[TransferFromToProgram]

,n.[OTYPE]

,n.[Old_FID]

,h1.Rsrc_Name AS Old_FP_Name

,h1.Orig_Cert AS Old_FP_Orig_Cert

,n.[NTYPE]

,n.[New_FID]

,h2.Rsrc_Name AS New_FP_Name

,h2.Orig_Cert AS New_FP_Orig_Cert

,n.PRIOR_EFFECT_DT

,m.Old_FID AS PRIOR_Old_FID

,n.FUTURE_EFFECT_DT

,f.New_FID AS FUTURE_New_FID

,c.STF_NBR

,c.Planner

,s.UNIT

,s.SupervisorName

,s.[SITE]

,s.DirectorName

,CASE WHEN (

n.NTYPE = 'KINS'

AND n.OTYPE <> 'KINS'

) THEN 1 WHEN n.ReasonKey IN (

'TE'

,'TJ'

) THEN 1 WHEN (

(n.New_FID = m.Old_FID)

AND (n.EFFECT_DT < DATEADD(D, 22, n.PRIOR_EFFECT_DT))

) THEN 1 ELSE 0 END AS POS_SCORE

,CASE WHEN (

n.NTYPE = 'KINS'

AND n.OTYPE <> 'KINS'

) THEN 0 WHEN n.ReasonKey IN (

'TE'

,'TJ'

) THEN 0 WHEN n.New_FID = m.Old_FID

AND n.EFFECT_DT < DATEADD(D, 22, n.PRIOR_EFFECT_DT) THEN 0 WHEN (

(n.Old_FID = f.New_FID)

AND (DATEADD(D, 22, n.EFFECT_DT) > n.FUTURE_EFFECT_DT)

) THEN 0 ELSE - 1 END AS NEG_SCORE

FROM NEW_MOVEMENTS n

LEFT OUTER JOIN MOVEMENTS m ON n.CLT_NBR = m.CLT_NBR

AND n.PRIOR_EFFECT_DT = m.EFFECT_DT

LEFT OUTER JOIN MOVEMENTS f ON n.CLT_NBR = f.CLT_NBR

AND n.FUTURE_EFFECT_DT = f.EFFECT_DT

LEFT OUTER JOIN GW_DW.dbo.DimClient c ON c.CLT_NBR = n.CLT_NBR

LEFT OUTER JOIN [ECMS].[dbo].[WFR_CLIENT] w ON w.CLT_NBR = n.CLT_NBR

LEFT OUTER JOIN [ECMS].[dbo].[CLIENT] cl ON cl.CLT_NBR = n.CLT_NBR

LEFT OUTER JOIN GW_DW.dbo.DimStaff s ON s.ECMS_Wrkr_ID = c.STF_NBR

LEFT OUTER JOIN [GW_DW].[dbo].[DimHome_FHD] h1 ON h1.Facility_ID = n.Old_FID

LEFT OUTER JOIN [GW_DW].[dbo].[DimHome_FHD] h2 ON h2.Facility_ID = n.New_FID

WHERE s.[SITE] <> 'CGS'

ORDER BY n.CHILD_NAME

,n.EFFECT_DT DESC

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Rick44 Mr or Mrs. 500 Points: 564 More actions · Answer 2

This is great!!! Thanks!!! I just tried to run this and yes, it takes a long time. If I wanted to turn this into a stored procedure how would I go that? I also need to use a user defined function so pulling from the queries below where CLT_NBR in (select clt_nbr from ufn_WFR_Enrolled_Clients_List ('2012-07-01', '2013-06-30')) Can one apply a unset defined function to a stored procedure?

OTF SSCertifiable Points: 6104 More actions · Answer 3

matt_garretson (7/23/2013)
This is great!!! Thanks!!! I just tried to run this and yes, it takes a long time. If I wanted to turn this into a stored procedure how would I go that? I also need to use a user defined function so pulling from the queries below where CLT_NBR in (select clt_nbr from ufn_WFR_Enrolled_Clients_List ('2012-07-01', '2013-06-30')) Can one apply a unset defined function to a stored procedure?

You would just add something along these lines at the start of the altered code you've been given:

CREATE PROC procName

@from_dt DATETIME,

@to_dt DATETIME

AS

Yes, you can refer to a UDF within a Stored Procedure.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

matt_garretson (7/23/2013)
This is great!!! Thanks!!! I just tried to run this and yes, it takes a long time. If I wanted to turn this into a stored procedure how would I go that? I also need to use a user defined function so pulling from the queries below where CLT_NBR in (select clt_nbr from ufn_WFR_Enrolled_Clients_List ('2012-07-01', '2013-06-30')) Can one apply a unset defined function to a stored procedure?

If you're interested in improving the performance of the view, post up an Actual (not Estimated) execution plan (as a .sqlplan file) of a simple select from it.

You have a few UDF's in there. If you are prepared to invest a little more time, folks here could show you how to replace a UDF with an iTVF (inline table-valued function). They often work very much faster.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

Be careful with the UDF. An in-line UDF is fine, but a multi-statement table valued UDF could lead to serious performance bottlenecks.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Rick44 Mr or Mrs. 500 Points: 564 More actions · Answer 6

Grant Fritchey (7/24/2013)
Be careful with the UDF. An in-line UDF is fine, but a multi-statement table valued UDF could lead to serious performance bottlenecks.

Hi great. Let me work on that today and I will get back to you. Creating the view takes way too long. I appreciate your help.