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
 Temp tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Damian39
Starting Member

USA
26 Posts

Posted - 07/25/2014 :  17:11:30  Show Profile  Reply with Quote
Hello all,
I'm trying to modify a query that was originally used as part of a GUI online report center. The query was built using three temp tables. I'm running into an Incorrect syntax error near the keyword 'CREATE'. It seems I have some incorrect coding between the first temp table and the second, but I haven't been able to identify it, and I was wondering if someone on this forum might be able to shed a little insight on this for me? Can someone please take a look at my code to let me know where I'm going wrong? Any and all help is greatly appreciated. Thanks

IF OBJECT_ID('TEMPDB..#TMP1A') IS NOT NULL DROP TABLE #TMP1A
IF OBJECT_ID('TEMPDB..#TEMPSUBS1') IS NOT NULL DROP TABLE #TEMPSUBS1
IF OBJECT_ID('TEMPDB..#TEMPSUBS2') IS NOT NULL DROP TABLE #TEMPSUBS2


CREATE TABLE #TMP1A (
ACCTCORP INT,
HOUSE INT,
CUST TINYINT,
BADATE VARCHAR,
AMOUNT VARCHAR,
ADJRSN VARCHAR,
BATCH VARCHAR,
CTLGRP VARCHAR
)


SELECT
ACCTCORP,
HOUSE,
CUST,
BADATE,
AMOUNT,
ADJRSN,
CASE WHEN BATCH BETWEEN 10000 AND 10999 THEN 'A' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 11000 AND 11999 THEN 'B' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 12000 AND 12999 THEN 'C' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 13000 AND 13999 THEN 'D' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 14000 AND 14999 THEN 'E' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 15000 AND 15999 THEN 'F' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 16000 AND 16999 THEN 'G' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 17000 AND 17999 THEN 'H' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 18000 AND 18999 THEN 'I' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 19000 AND 19999 THEN 'J' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 20000 AND 20999 THEN 'K' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 21000 AND 21999 THEN 'L' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 22000 AND 22999 THEN 'M' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 23000 AND 23999 THEN 'N' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 24000 AND 24999 THEN 'O' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 25000 AND 25999 THEN 'P' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 26000 AND 26999 THEN 'Q' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 27000 AND 27999 THEN 'R' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 28000 AND 28999 THEN 'S' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 29000 AND 29999 THEN 'T' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 30000 AND 30999 THEN 'U' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 31000 AND 31999 THEN 'V' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 32000 AND 32999 THEN 'W' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 33000 AND 33999 THEN 'X' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 34000 AND 34999 THEN 'Y' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 35000 AND 35999 THEN 'Z' + RIGHT(BATCH,3)
WHEN CONVERT(VARCHAR,BATCH) <= 9999 THEN CONVERT(VARCHAR,BATCH)
ELSE '' END AS 'DDP_BATCH_#',BATCH AS [ORIG_BATCH_#],
--BATCH,
CTLGRP,
CASE WHEN POSTFLAG = ' ' THEN 'No'
ELSE 'Yes' END AS POSTFLAG

INSERT INTO #TMP1A (ACCTCORP,HOUSE,CUST,BADATE,AMOUNT,ADJRSN,BATCH,CTLGRP,POSTFLAG)

--FROM OPENQUERY(/*INFODDP*/THUNDER_ETL,

--FROM OPENQUERY(DSSOPT_DDP,

--'
--SELECT
--BC.ACCTCORP,BD.HOUSE,BD.CUST,BC.BADATE,BD.AMOUNT,BD.ADJRSN,BC.BATCH,BC.CTLGRP,BD.POSTFLAG
--FROM DDPDB.IDST_BATCH_CATALOG BC JOIN DDPDB.IDST_BATCH_DETAIL BD
--ON(BC.ACCTCORP=BD.ACCTCORP) AND (BC.BATCH=BD.BATCH)
--WHERE BD.ACCTCORP IN (''1624'', ''1638'', ''1641'', ''1643'', ''1710'',''01105'',''01636'',''01719'',''09587'',''15515'',''19204'')
--AND BC.CTLGRP = ''REFND'' AND BD.AMOUNT >= ''500.00''
--')


CREATE TABLE #TEMPSUBS1 (
AcctCorp INT,
House INT,
Cust TINYINT,
Ftax TINYINT,
BADR CHAR(1) DEFAULT ' '
)

INSERT INTO #TEMPSUBS1 (AcctCorp,House,Cust,Ftax,BADR)


SELECT A.ACCTCORP,A.HOUSE,A.CUST,H.Ftax,C.BADR

FROM #TMP1A A INNER JOIN [INFODDPFlorida].[ggs].[IDST_CUSTOMER] C (NOLOCK)
ON A.ACCTCORP=C.ACCTCORP AND
A.HOUSE=C.HOUSE AND
A.CUST=C.CUST

INNER JOIN [INFODDPFlorida].[ggs].[IDST_HOUSE] H (NOLOCK)
ON C.ACCTCORP=H.ACCTCORP AND
C.HOUSE=H.HOUSE

CREATE TABLE #TEMPSUBS2 (
ACCTCORP INT,
HOUSE INT,
CUST TINYINT,
CARE_OF_ADDRESS_LINE1 VARCHAR(50),
ADDRESS_LINE2 VARCHAR(50),
ADDRESS_LINE3 VARCHAR(50),
ADDRESS_LINE4 VARCHAR(50)
)

INSERT INTO #TEMPSUBS2
(ACCTCORP,HOUSE,CUST,CARE_OF_ADDRESS_LINE1,ADDRESS_LINE2,ADDRESS_LINE3,ADDRESS_LINE4)

SELECT A.ACCTCORP,A.HOUSE,A.CUST,' ',
INFODDPFlorida.dbo.udf_ConcatAddress(H.STNUM,H.FRACT,H.DIR,H.NAME,'','') + ' ' + H.APT + ' ' + H.APTN AS [ADDRESS_LINE2],
Z.CITY + ' ' + Z.STATE AS [ADDRESS_LINE3],
LEFT(H.ZIPCODE,5) + '-' + H.ZIP4 AS [ADDRESS_LINE4]

FROM #TEMPSUBS1 A INNER JOIN INFODDPFlorida.GGS.IDST_HOUSE H (NOLOCK)
ON A.ACCTCORP=H.ACCTCORP AND
A.HOUSE=H.HOUSE

LEFT JOIN INFODDPFlorida.GGS.IDST_ZIP Z (NOLOCK)
ON H.ACCTCORP=Z.ACCTCORP AND
H.ZIPCODE BETWEEN Z.ZIPBEG AND Z.ZIPEND

WHERE A.BADR IN (' ','1')

INSERT INTO #TEMPSUBS2
(ACCTCORP,HOUSE,CUST,CARE_OF_ADDRESS_LINE1,ADDRESS_LINE2,ADDRESS_LINE3,ADDRESS_LINE4)

SELECT A.ACCTCORP,A.HOUSE,A.CUST,
ISNULL(G.BIL1,' ') AS [BILLING ADDR LINE 1],
ISNULL(G.BIL2,' ') AS [BILLING ADDR LINE 2],
ISNULL(G.BIL3,' ') AS [BILLING ADDR LINE 3],
ISNULL(G.BIL4,' ') AS [BILLING ADDR LINE 4]

FROM #TEMPSUBS1 A LEFT JOIN INFODDPFlorida.GGS.IDST_CUST_BILLING_ADDRESS G (NOLOCK)
ON A.ACCTCORP=G.ACCTCORP AND
A.HOUSE=G.HOUSE AND
A.CUST=G.CUST

WHERE A.BADR IN ('2')


SELECT DISTINCT CONVERT(VARCHAR(8), GETDATE(), 1) AS ReportDate,
RIGHT('00000' + CAST(A.ACCTCORP AS VARCHAR),5) +
RIGHT('000000' + CAST(A.HOUSE AS VARCHAR), 6) +
RIGHT('00' + CAST(A.CUST AS VARCHAR),2) AS CorpHouseCust,
A.AcctCorp,A.House,A.Cust,H.Ftax,
CASE WHEN C.STAT = '1' THEN 'PendingInstall'
WHEN C.STAT = '2' THEN 'PendingRestart'
WHEN C.STAT = '3' THEN 'PendingChgOfSvc'
WHEN C.STAT = '4' THEN 'PendingDisco'
WHEN C.STAT = '5' THEN 'Active'
WHEN C.STAT = '6' THEN 'Disconnect'
WHEN C.STAT = '7' THEN 'CancelledInstall'
ELSE '' END AS 'CurrentCustomerStatus',
--C.DRSN,
CASE WHEN C.CUSTOMER_TYPE = 'B' THEN 'Business'
WHEN C.CUSTOMER_TYPE = 'R' THEN 'Residential'
ELSE '' END AS 'CustomerType',
C.AnnivDay,
C.DDP_Cycle AS 'Cycle',
ISNULL(CONVERT(VARCHAR(10),AA.BADATE,120),' ') AS BatchDate,
--AA.BADATE,
AA.AMOUNT AS 'RefundAmt',
AA.ADJRSN,AA.[DDP_BATCH_#],AA.[ORIG_BATCH_#],AA.CTLGRP,AA.POSTFLAG,

(C.LNAME + ',' + C.FNAME) AS CustomerName,
CAST(C.RAREACD AS VARCHAR(3)) + C.RPHON AS ResPhone,
RIGHT(C.BPHONE,3) + LEFT(C.BPHONE,7) AS BusinessPhone,

INFODDPFlorida.dbo.udf_ConcatAddress(H.STNUM,H.FRACT,H.DIR,H.NAME,'','') AS ServiceAddress,
H.APT + ' ' + H.APTN AS ServiceAptAndNumber,Z.CITY AS ServiceCity,
Z.STATE AS ServiceState,H.ZIPCODE + '-' + H.ZIP4 AS ServiceZipAndZip4,

A.CARE_OF_ADDRESS_LINE1 AS MailToCareOfAddressLine1,
A.ADDRESS_LINE2 AS MailToAddressLine2,
A.ADDRESS_LINE3 AS MailToAddressLine3,
A.ADDRESS_LINE4 AS MailToAddressLine4,
D.LONGDESC AS DwellingType,H.COMPLEX AS ComplexCode,
ISNULL(HC.CNAME, ' ') AS ComplexName,
C.RATE AS MonthlyRate,
(C.CUR+C.C30+C.C60+C.C90+C.C120) AS TotalBalance --,

FROM #TEMPSUBS2 A INNER JOIN INFODDPFlorida.GGS.IDST_CUSTOMER C (NOLOCK)
ON A.ACCTCORP=C.ACCTCORP AND
A.HOUSE=C.HOUSE AND
A.CUST=C.CUST

INNER JOIN #TMP1A AA
ON C.ACCTCORP=AA.ACCTCORP AND
C.HOUSE=AA.HOUSE AND
C.CUST=AA.CUST

INNER JOIN INFODDPFlorida.GGS.IDST_HOUSE H (NOLOCK)
ON C.ACCTCORP=H.ACCTCORP AND
C.HOUSE=H.HOUSE

LEFT JOIN INFODDPFlorida.GGS.IDST_DWELLING D (NOLOCK)
ON H.ACCTCORP=D.ACCTCORP AND
H.DWELL=D.CODE

LEFT JOIN INFODDPFlorida.GGS.IDST_COMPLEX HC (NOLOCK)
ON H.ACCTCORP=HC.ACCTCORP AND
H.COMPLEX=HC.COMPLEX

LEFT JOIN INFODDPFlorida.GGS.IDST_ZIP Z (NOLOCK)
ON H.ACCTCORP=Z.ACCTCORP AND
H.ZIPCODE BETWEEN Z.ZIPBEG AND Z.ZIPEND

WHERE C.DRSN NOT IN ('9','G','N')


Damian

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/25/2014 :  17:14:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
The issue is here: INSERT INTO #TMP1A (ACCTCORP,HOUSE,CUST,BADATE,AMOUNT,ADJRSN,BATCH,CTLGRP,POSTFLAG). It's line 58, or you can double click on the error to jump up to the CREATE, then look up a few for the INSERT.

It is missing the second part, such as VALUES, SELECT or EXEC. What do you want to insert into it? You need to add code there.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Damian39
Starting Member

USA
26 Posts

Posted - 07/26/2014 :  00:38:28  Show Profile  Reply with Quote
Hi Tara. Thanks for replying. I thought i did have a SELECT statement after the CREATE TABLE. Am I missing something that should go before the SELECT statement I have in there? I haven't done any serious coding on a while, so I'm a bit rusty with temp tables. I appreciate the help.

Damian
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/26/2014 :  15:11:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
You don't have a SELECT query there. You have an INSERT and then a CREATE, but nothing in between. Hence the error.

This is not a temp table issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Damian39
Starting Member

USA
26 Posts

Posted - 07/28/2014 :  09:09:38  Show Profile  Reply with Quote
Hi Tara, I took another look at the code, and now I see what you're saying. I have my SELECT statement in the wrong area. If I'm understanding this correctly, I would need a SELECT statement after the INSERT INTO portion of the code, and I need to join my temp table to a separate table. Is this correct?


IF OBJECT_ID('TEMPDB..#TMP1A') IS NOT NULL DROP TABLE #TMP1A
IF OBJECT_ID('TEMPDB..#TEMPSUBS1') IS NOT NULL DROP TABLE #TEMPSUBS1
IF OBJECT_ID('TEMPDB..#TEMPSUBS2') IS NOT NULL DROP TABLE #TEMPSUBS2


CREATE TABLE #TMP1A (
ACCTCORP INT,
HOUSE INT,
CUST TINYINT,
BADATE VARCHAR,
AMOUNT VARCHAR,
ADJRSN VARCHAR,
BATCH VARCHAR,
CTLGRP VARCHAR
)


INSERT INTO #TMP1A (ACCTCORP,HOUSE,CUST,BADATE,AMOUNT,ADJRSN,BATCH,CTLGRP,POSTFLAG)

SELECT
ACCTCORP,
HOUSE,
CUST,
BADATE,
AMOUNT,
ADJRSN,
CASE WHEN BATCH BETWEEN 10000 AND 10999 THEN 'A' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 11000 AND 11999 THEN 'B' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 12000 AND 12999 THEN 'C' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 13000 AND 13999 THEN 'D' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 14000 AND 14999 THEN 'E' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 15000 AND 15999 THEN 'F' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 16000 AND 16999 THEN 'G' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 17000 AND 17999 THEN 'H' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 18000 AND 18999 THEN 'I' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 19000 AND 19999 THEN 'J' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 20000 AND 20999 THEN 'K' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 21000 AND 21999 THEN 'L' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 22000 AND 22999 THEN 'M' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 23000 AND 23999 THEN 'N' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 24000 AND 24999 THEN 'O' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 25000 AND 25999 THEN 'P' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 26000 AND 26999 THEN 'Q' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 27000 AND 27999 THEN 'R' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 28000 AND 28999 THEN 'S' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 29000 AND 29999 THEN 'T' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 30000 AND 30999 THEN 'U' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 31000 AND 31999 THEN 'V' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 32000 AND 32999 THEN 'W' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 33000 AND 33999 THEN 'X' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 34000 AND 34999 THEN 'Y' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 35000 AND 35999 THEN 'Z' + RIGHT(BATCH,3)
WHEN CONVERT(VARCHAR,BATCH) <= 9999 THEN CONVERT(VARCHAR,BATCH)
ELSE '' END AS 'DDP_BATCH_#',BATCH AS [ORIG_BATCH_#],
--BATCH,
CTLGRP,
CASE WHEN POSTFLAG = ' ' THEN 'No'
ELSE 'Yes' END AS POSTFLAG

FROM #TMP1A AS TM INNER JOIN ...

Edited by - Damian39 on 07/28/2014 09:24:31
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/28/2014 :  12:12:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
I can't tell you if the code does what you want as you haven't provided any detail in that regard, but yes the select needs to be after the insert. We can only help with the syntax issue given the information thus far.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Damian39
Starting Member

USA
26 Posts

Posted - 07/28/2014 :  13:09:10  Show Profile  Reply with Quote
Hi Tara, the code is supposed to find any accounts that have been issued a refund of $500 or more. I am trying to modify the query to pull any refunds that were given, no matter the amount, and also whether these accounts have any equipment still linked to them or not. Unfortunately, until I can get past this temp table issue I'm dealing with, I can't progress any further with the modifications I would like to make to the original query.

Damian
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/28/2014 :  13:37:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
You will need to greatly simplify it and come up with a test case for us: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
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