

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




SSCertifiable
Group: General Forum Members
Last Login: Today @ 7:24 PM
Points: 7,690,
Visits: 16,609


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




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.




SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 9:26 AM
Points: 8,429,
Visits: 18,263


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




SSCoach
Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 16,862,
Visits: 31,629





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.



