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
 Temp tables

Author  Topic 

Damian39
Starting Member

33 Posts

Posted - 2014-07-25 : 17:11:30
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

38200 Posts

Posted - 2014-07-25 : 17:14:40
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

33 Posts

Posted - 2014-07-26 : 00:38:28
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

38200 Posts

Posted - 2014-07-26 : 15:11:50
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

33 Posts

Posted - 2014-07-28 : 09:09:38
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 ...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 12:12:01
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

33 Posts

Posted - 2014-07-28 : 13:09:10
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

38200 Posts

Posted - 2014-07-28 : 13:37:20
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
   

- Advertisement -