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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 4,046, Visits: 9,202
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.
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?

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: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
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 Query Performance Tuning
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