Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using a Temporary Table in a View

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 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
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] 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 - 2013-07-23 : 18:17:48
This is great! thanks so much!
Go to Top of Page

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
Go to Top of Page

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 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 - 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!
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -