Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using a Temporary Table in a View in Order to Combine three Queries Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 3:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
Points: 30, Visits: 107

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
Post #1476816
Posted Tuesday, July 23, 2013 4:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 3,360, Visits: 7,275
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1476822
Posted Tuesday, July 23, 2013 4:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
Points: 30, Visits: 107
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?
Post #1476829
Posted Wednesday, July 24, 2013 3:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
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.
Post #1476946
Posted Wednesday, July 24, 2013 4:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,129, Visits: 13,510
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1476964
Posted Wednesday, July 24, 2013 5:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 15,537, Visits: 27,914
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1476988
Posted Wednesday, July 24, 2013 8:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
Points: 30, Visits: 107
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.
Post #1477078
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse