Author |
Topic |
mgarret
Starting Member
14 Posts |
Posted - 2013-07-23 : 17:35:59
|
Hi all. I am trying to create a view using to 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 1SELECT * INTO #MOVEMENTS FROM [GW_DW].[dbo].[DimStatusHistory] dWHERE TransferFromToProgram<>'' AND d.Status=12;--Query 2SELECT 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 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-23 : 17:51:46
|
You can't use temp tables in a view definition, nor can you use or pass in variables. So something like shown below. A stored procedure may be more appropriate for you here. --Query 1;WITH MOVEMENTS AS(SELECT *FROM [GW_DW].[dbo].[DimStatusHistory] dWHERE TransferFromToProgram <> '' AND d.Status = 12),NEW_MOVEMENTS AS(--Query 2SELECT 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_NBRWHERE n.TransferFromToProgram NOT IN ( '', 'FBH - TFBH', 'TFBH - FBH' ) -- YOU CAN USE PARAMETERS IN A VIEW DEFINTION --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 3SELECT 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_SCOREFROM 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_FIDWHERE s.[SITE] <> 'CGS'ORDER BY n.CHILD_NAME , n.EFFECT_DT DESC |
|
|
mgarret
Starting Member
14 Posts |
Posted - 2013-07-23 : 18:17:48
|
This is great! thanks so much! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-23 : 18:19:18
|
There is some major flawed logic in query 2. You do LEFT JOIN against #Movement table, but doesn't filter nor display any columns from that table.Hence, there is absolutely no need to do the LEFT JOIN at all! Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-23 : 18:41:01
|
You can also use an IVTF (Inline Table Valued Function), which accepts parameters.CREATE FUNCTION dbo.fnLargeFunction( @from_dt DATETIME, @to_dt DATETIME)RETURNS TABLEASRETURN ( WITH cteRaw AS ( SELECT DISTINCT CLT_NBR, CHILD_NAME, ReasonKey, ReasonDesc, EFFECT_DT, [Status], STATUS_DESC, DESCRIPT, TRAN_TYPE, OTYPE, Old_FID, NTYPE, New_FID, TransferFromToProgram, TOTAL_DAYS FROM GW_DW.dbo.DimStatusHistory WHERE TransferFromToProgram NOT IN ('', 'FBH - TFBH', 'TFBH - FBH') AND EFFECT_DT BETWEEN @from_dt AND @to_dt AND COUNTY = 'NYC' AND OTYPE NOT IN ('RTC', 'SLIP') AND NTYPE NOT IN ('RTC', 'SLIP') AND [Status] = 12 ), cteSource AS ( SELECT CLT_NBR, CHILD_NAME, ReasonKey, ReasonDesc, EFFECT_DT, [Status], STATUS_DESC, DESCRIPT, TRAN_TYPE, OTYPE, Old_FID, NTYPE, New_FID, TransferFromToProgram, ECMS.dbo.FN_PRIOR_EFFECT_DT_FOR_STATUS(EFFECT_DT, CLT_NBR, 12) AS PRIOR_EFFECT_DT, ECMS.dbo.FN_NEXT_EFFECT_DT_FOR_STATUS(EFFECT_DT, CLT_NBR, 12) AS FUTURE_EFFECT_DT, TOTAL_DAYS FROM cteRaw ) 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(DAY, 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(DAY, 22, n.PRIOR_EFFECT_DT) THEN 0 WHEN n.Old_FID = f.New_FID AND DATEADD(DAY, 22, n.EFFECT_DT) > n.FUTURE_EFFECT_DT THEN 0 ELSE -1 END AS NEG_SCORE FROM cteSource AS n INNER JOIN GW_DW.dbo.DimClient AS c ON c.CLT_NBR = n.CLT_NBR INNER JOIN GW_DW.dbo.DimStaff AS s ON s.ECMS_Wrkr_ID = c.STF_NBR AND s.[SITE] <> 'CGS' LEFT JOIN GW_DW.dbo.DimHome_FHD AS h1 ON h1.Facility_ID = n.Old_FID LEFT JOIN GW_DW.dbo.DimHome_FHD AS h2 ON h2.Facility_ID = n.New_FID LEFT JOIN GW_DW.dbo.DimStatusHistory AS m ON m.CLT_NBR = n.CLT_NBR AND m.EFFECT_DT = n.PRIOR_EFFECT_DT AND m.TransferFromToProgram > '' AND m.[Status] = 12 LEFT JOIN GW_DW.dbo.DimStatusHistory AS f ON f.CLT_NBR = n.CLT_NBR AND f.EFFECT_DT = n.FUTURE_EFFECT_DT AND f.TransferFromToProgram > '' AND f.[Status] = 12 LEFT JOIN ECMS.dbo.WFR_CLIENT AS w ON w.CLT_NBR = n.CLT_NBR LEFT JOIN ECMS.dbo.CLIENT AS cl ON cl.CLT_NBR = n.CLT_NBR )GOSELECT * FROM dbo.fnLargeFunction('20130101', '20131231') Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
mgarret
Starting Member
14 Posts |
Posted - 2013-07-23 : 20:04:54
|
Thanks so much! yes, I inherited this program so I am still trying to figure it out. I takes a very very long time to run! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-24 : 01:56:25
|
I would guess that these two functions (ECMS.dbo.FN_PRIOR_EFFECT_DT_FOR_STATUS and ECMS.dbo.FN_NEXT_EFFECT_DT_FOR_STATUS) are the ones that slows the code down.Try to comment these two lines out.Or hardwire two arbitrary dates as PRIOR_EFFECT_DT and NEXT_EFFECT_DT, to see what happens. My estimate is that the code will run MUCH faster.The reason for this is that scalar functions doesn't run that well in SQL Server. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|