SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using a Temporary Table in a View
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mgarret
Starting Member

14 Posts

Posted - 07/23/2013 :  17:35:59  Show Profile  Reply with Quote
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 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

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/23/2013 :  17:51:46  Show Profile  Reply with Quote
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] d
WHERE   TransferFromToProgram <> ''
        AND d.Status = 12
),
NEW_MOVEMENTS AS
(
--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]
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' )
	-- 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 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
Go to Top of Page

mgarret
Starting Member

14 Posts

Posted - 07/23/2013 :  18:17:48  Show Profile  Reply with Quote
This is great! thanks so much!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/23/2013 :  18:19:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/23/2013 :  18:41:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can also use an IVTF (Inline Table Valued Function), which accepts parameters.
CREATE FUNCTION	dbo.fnLargeFunction
(
	@from_dt DATETIME,
	@to_dt DATETIME
)
RETURNS TABLE
AS
RETURN	(
		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
	)
GO

SELECT * FROM dbo.fnLargeFunction('20130101', '20131231')



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

mgarret
Starting Member

14 Posts

Posted - 07/23/2013 :  20:04:54  Show Profile  Reply with Quote
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!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/24/2013 :  01:56:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000