

SSC 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




SSCrazy
Group: General Forum Members
Last Login: Today @ 6:10 PM
Points: 2,763,
Visits: 5,909


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




SSC 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 ('20120701', '20130630')) Can one apply a unset defined function to a stored procedure?




Old 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 ('20120701', '20130630')) 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.




SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 9:33 AM
Points: 6,754,
Visits: 12,854


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 ('20120701', '20130630')) 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 tablevalued 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




SSChampion
Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802,
Visits: 27,276





SSC 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 inline UDF is fine, but a multistatement 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.



