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 tmp table prior to create table

Author  Topic 

Damian39
Starting Member

33 Posts

Posted - 2014-10-17 : 10:29:02
I created a query that originally started out with me creating four tables, and then adding temp tables below them. I attempted to add another temp table at the end, but it isn't yielding any information. I discovered the informtion I am attempting to retrieve is archived to different tables, therefore I will need to first retrieve all the data within the archived tables, and then add that to the query I originally wrote. I'm running into difficulty trying to figure out the best way to add this new portion to my query. I've added the code below to show you what I have so far. First issue I know I have is I am not connecting both parts therefore when the query runs, I get two results. I have to union multiple tables together in order to retrieve all of the results I want for the data range I have set. Here's the code. Please dissect as you see fit, and reach out to me for further explanations. Towards the bottom, table #TMP3C is only bringing back NULL values, and that's because I am using the current equip base table and none of the archived tables. Should I add the new code to the bottom of my current code?Thanks!


IF OBJECT_ID('TEMPDB..#TMP1CE') IS NOT NULL DROP TABLE #TMP1CE
IF OBJECT_ID('TEMPDB..#TMPCSGACCT1') IS NOT NULL DROP TABLE #TMPCSGACCT1
IF OBJECT_ID('TEMPDB..#TMPCSGACCT2') IS NOT NULL DROP TABLE #TMPCSGACCT2
IF OBJECT_ID('TEMPDB..#TMPCSGACCT3') IS NOT NULL DROP TABLE #TMPCSGACCT3
IF OBJECT_ID('TEMPDB..#TMPCSGACCT4') IS NOT NULL DROP TABLE #TMPCSGACCT4
IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C
IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C
IF OBJECT_ID('TEMPDB..#TMP3C') IS NOT NULL DROP TABLE #TMP3C



SELECT DISTINCT [PRIN_EQL], [EQP_SERIAL_EQL], [EQP_LOCAL_EQL], [EQP_STAT_EQL],
[START_DTE_EQL]

INTO #TMP1CE

FROM [Vantage].[dbo].[EQL_HIST] (NOLOCK)

WHERE EQP_STAT_EQL IN ('R','L')
AND EQP_LOCAL_EQL LIKE ('8495%')
AND [START_DTE_EQL] BETWEEN '2013-10-01' AND '2014-09-30'

SELECT DISTINCT A.*,EQ.[EQP_LOCAL_EQP],EQ.[EQP_SERIAL_EQP],EQN.[EQP_STAT_EQP] AS EquipStatNow
FROM #TMP1CE A WITH(NOLOCK) LEFT JOIN [Vantage].[dbo].[EQP_BASE20140921] EQ WITH(NOLOCK)
ON A.PRIN_EQL=EQ.PRIN_EQP
AND A.[EQP_SERIAL_EQL]=EQ.[EQP_SERIAL_EQP]
AND A.[EQP_STAT_EQL] = EQ.[EQP_STAT_EQP]

LEFT JOIN [Vantage].[dbo].[EQP_BASE] EQN WITH(NOLOCK)
ON EQ.PRIN_EQP=EQN.PRIN_EQP
AND EQ.[EQP_SERIAL_EQP]=EQN.[EQP_SERIAL_EQP]
AND EQN.PRIN_EQP IN ('6000','7500')




CREATE TABLE #TMPCSGACCT1 (
ITEM# NUMERIC (7,0),
SUB_ACCT_NO VARCHAR (MAX)
)
INSERT INTO #TMPCSGACCT1 (ITEM#,SUB_ACCT_NO)

SELECT ITEM#,
rtrim(ltrim([SUB_ACCT_NO])) AS SUB_ACCT_NO

FROM [AuditGroup].dbo.EquipRetDataTest (NOLOCK)

CREATE TABLE #TMPCSGACCT2 (
ITEM# NUMERIC (7,0),
SUB_ACCT_NO VARCHAR (MAX)
)

INSERT INTO #TMPCSGACCT2 (ITEM#,SUB_ACCT_NO)

SELECT ITEM#,SUB_ACCT_NO
FROM #TMPCSGACCT1 WITH(NOLOCK)
WHERE LEFT([SUB_ACCT_NO],6) IN ('849560','849575')

CREATE TABLE #TMPCSGACCT3 (
ITEM# NUMERIC (7,0),
PRIN SMALLINT,
SUB_ACCT_NO CHAR (16)
)
INSERT INTO #TMPCSGACCT3 (ITEM#,PRIN,SUB_ACCT_NO)

SELECT ITEM#,
CASE WHEN LEFT([SUB_ACCT_NO],6) = '849560' THEN '6000'
--WHEN LEFT([SUB_ACCT_NO],6) = '849574' THEN '7400'
WHEN LEFT([SUB_ACCT_NO],6) = '849575' THEN '7500'
ELSE '' END AS PRIN,
SUB_ACCT_NO

FROM #TMPCSGACCT2 WITH(NOLOCK)

CREATE TABLE #TMPCSGACCT4 (
ITEM# NUMERIC (7,0),
PRIN SMALLINT,
SUB_ACCT_NO CHAR (16),
HSE_KEY_SBB CHAR (14),
[CUST_ACCT_NO_SBB] CHAR(13),
[RES_NAME_SBB] VARCHAR (26),
[CUR_BAL_SBB] DECIMAL(9,2)
)

INSERT INTO #TMPCSGACCT4 (ITEM#,PRIN,SUB_ACCT_NO,[HSE_KEY_SBB],[CUST_ACCT_NO_SBB],[RES_NAME_SBB],[CUR_BAL_SBB])

SELECT A.ITEM#,A.PRIN,A.SUB_ACCT_NO,S.[HSE_KEY_SBB],S.[CUST_ACCT_NO_SBB],S.[RES_NAME_SBB], S.[CUR_BAL_SBB]

FROM #TMPCSGACCT3 A WITH(NOLOCK) INNER JOIN [Vantage].[dbo].[SBB_BASE] S WITH(NOLOCK)
ON A.PRIN=S.PRIN_SBB AND
A.SUB_ACCT_NO=S.SUB_ACCT_NO_SBB AND S.PRIN_SBB IN ('6000','7500')


select
T4.PRIN,
T4.SUB_ACCT_NO,
T4.HSE_KEY_SBB,
T4.RES_NAME_SBB,
T4.CUR_BAL_SBB,
h.acct_stage_ohi,
h.connect_dte_ohi,
h.disco_dte_ohi,
h.item_status_ohi


INTO #TMP1C

from #TMPCSGACCT4 as T4 (nolock) inner join vantage.dbo.ohi_hist_item as h (nolock)

ON T4.PRIN = H.PRIN_OHI
and T4.SUB_ACCT_NO = h.SUB_ACCT_NO_OHI and h.prin_ohi in ('6000','7500')

where h.DISCO_DTE_OHI > h.CONNECT_DTE_OHI AND
H.ITEM_STATUS_OHI IN ('C','O','P','B')
and h.acct_stage_ohi in ('V','N')
and t4.item# = 1


SELECT A.PRIN,
A.SUB_ACCT_NO,
A.ACCT_STAGE_OHI,
A.RES_NAME_SBB,
HS.ADDR1_HSE,
HS.RES_CITY_HSE,
HS.RES_STATE_HSE,
HS.POSTAL_CDE_HSE,
A.CUR_BAL_SBB,
A.CONNECT_DTE_OHI,
max(A.disco_dte_ohi) as disco_dte_ohi

INTO #TMP2C


FROM #TMP1C A WITH(NOLOCK) INNER JOIN Vantage.dbo.HSE_BASE HS WITH (NOLOCK)

ON A.HSE_KEY_SBB = HS.HSE_KEY_HSE
--AND A.SUB_ACCT_NO = HS.SUB_ACCT_NO_HSE

GROUP BY A.PRIN,
A.SUB_ACCT_NO,
A.ACCT_STAGE_OHI,
A.RES_NAME_SBB,
HS.ADDR1_HSE,
HS.RES_CITY_HSE,
HS.RES_STATE_HSE,
HS.POSTAL_CDE_HSE,
A.CUR_BAL_SBB,
A.CONNECT_DTE_OHI

SELECT B.PRIN,
B.SUB_ACCT_NO,
B.ACCT_STAGE_OHI,
B.RES_NAME_SBB,
B.ADDR1_HSE,
B.RES_CITY_HSE,
B.RES_STATE_HSE,
B.POSTAL_CDE_HSE,
M.TRAN_AMT_MON,
B.CUR_BAL_SBB,
B.CONNECT_DTE_OHI,
B.disco_dte_ohi,
M.TRAN_DTE_MON

INTO #TMP3C

FROM #TMP2C B WITH (NOLOCK) INNER JOIN Vantage.dbo.MON_TRAN_BASE M WITH (NOLOCK)

ON B.PRIN = M.PRIN_MON
AND B.SUB_ACCT_NO = M.SUB_ACCT_NO_MON

WHERE M.TRAN_CDE_MON = '287'

SELECT C.PRIN,
C.SUB_ACCT_NO AS ACCOUNT_NUMBER,
CASE WHEN C.ACCT_STAGE_OHI = 'V' THEN 'VOL_DISCONNECT'
WHEN C.ACCT_STAGE_OHI = 'N' THEN 'NON_PAY_DISCONNECT'
ELSE '' END AS 'CUSTOMER_STATUS',
C.RES_NAME_SBB AS CUSTOMER_NAME,
C.ADDR1_HSE AS [ADDRESS],
C.RES_CITY_HSE AS CITY,
C.RES_STATE_HSE AS [STATE],
C.POSTAL_CDE_HSE AS ZIP_CODE,
C.TRAN_AMT_MON AS REFUND_AMOUNT,
C.CUR_BAL_SBB AS CURRENT_BALANCE,
C.CONNECT_DTE_OHI AS CONNECT_DATE,
C.disco_dte_ohi AS DISCONNECT_DATE,
C.TRAN_DTE_MON AS REFUND_DATE,
EQ.STAT_CHG_DTE_EQP,
EQ.CREATE_STAT_EQP,
EQ.BOX_STAT_EQP,
EQ.EQP_STAT_EQP,
--EQ2.EQP_STAT_EQL,
--EQ2.EQP_SERIAL_EQL
EQ.EQP_SERIAL_EQP

FROM #TMP3C C WITH (NOLOCK) LEFT OUTER JOIN Vantage.dbo.EQP_BASE EQ WITH (NOLOCK)

ON C.SUB_ACCT_NO = EQ.EQP_LOCAL_EQP


Damian

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-17 : 12:03:44
So, in the query that builds temp2c, what happens when you run it by itself, commenting out the INTO clause? That's where you need to look.

And...get rid of the NOLOCK hints! You've got financial data there. You cannot afford dirty reads. also, NOLOCK against a temp table is useless, since it is unique per session anyway.
Go to Top of Page

Damian39
Starting Member

33 Posts

Posted - 2014-10-17 : 12:47:54
Thanks Gbritton. The nolocks is just habit. I'm aware I don't need to have them for the temp tables I'm creating. I just prefer to use them, because I am also hitting actual DB tables, and I don't want to lock out any other users that may need access to one of those tables. My #TMP2C table is working fine. Actually all of my tables are working fine, I'm just having difficulty figuring out how to add the portion in the beginning that is being used to hit archived tables in order to get serial number, and remove date data.

Damian
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-17 : 13:01:10
I kicked the NOLOCK habit and I'm a better man for it! (Though I do use READPAST now and then.) You should visit NOLOCKs Anonymous some time...

Seriously, though, what I'd do is work forward validating the results as I go until I spot something incorrect, then work backward from that point to see where it occurred.
Go to Top of Page
   

- Advertisement -