Help with Syntax error

Question

Help with Syntax error

mlwiller2

SSChasing Mays

Points: 617
More actions
April 23, 2014 at 12:53 pm

#148390

Having some trouble with a syntax error related to the FROM statements on line 65 and 95:
Use PARCS_DB
GO
TRUNCATE TABLE tbl_Utilities_PARCS_LiveValue
TRUNCATE TABLE tbl_Utilities_PARCS_Totals
TRUNCATE TABLE tbl_Utilities_PARCS_YTD
TRUNCATE TABLE tbl_Utilities_CUB_LiveValue
TRUNCATE TABLE tbl_Utilities_CUB_Totals
TRUNCATE TABLE tbl_Utilities_CUB_YTD
TRUNCATE TABLE tbl_Utilities_BLDG1_LiveValue
TRUNCATE TABLE tbl_Utilities_BLDG1_Totals
TRUNCATE TABLE tbl_Utilities_BLDG1_YTD
GO
DECLARE @Year INT;
SET @Year = 2014;
-- CONVERT TO A DATE TO ALLOW A SARGEABLE PREDICATE IN THE WHERE CLAUSE
DECLARE @Date SMALLDATETIME;
SET @Date = CONVERT(SMALLDATETIME, CONVERT(CHAR(4), @Year), 112);
-- GET PF OLDTOTAL VALUES FROM HISTORY.
INSERT INTO PARCS_DB.dbo.tbl_Utilities_PARCS_Totals
(Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
SELECT
Jan = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 2 THEN value END),
Feb = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 3 THEN value END),
Mar = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 4 THEN value END),
Apr = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 5 THEN value END),
May = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 6 THEN value END),
Jun = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 7 THEN value END),
Jul = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 8 THEN value END),
Aug = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 9 THEN value END),
Sep = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 10 THEN value END),
Oct = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 11 THEN value END),
Nov = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 12 THEN value END),
Dec = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 1 AND DATEPART(YEAR,DateTime) = DATEADD(YEAR, 1, @Date) THEN value END)
FROM runtime.dbo.History
WHERE Tagname IN ('FQI_PA_B_001.OldTotal','FQI_PS_B_001.OldTotal','FQI_CW_B_002.OldTotal','FQI_NCW_B_001.OldTotal','FQI_NCW_B_002.OldTotal')
AND wwVersion = 'Latest'
AND DateTime >= @Date
AND DateTime < DATEADD(YEAR, 1, @Date)
GROUP BY TagName;
-- GET PF LIVE VALUES.
INSERT INTO PARCS_DB.dbo.tbl_Utilities_PARCS_LiveValue
(Value)
SELECT Value
FROM runtime.dbo.v_live
WHERE Tagname IN ('FQI_CW_B_002.Total','FQI_NCW_B_001.Total','FQI_NCW_B_002.Total','FQI_PA_B_001.Total','FQI_PS_B_001.Total')
-- GET CUB OLDTOTAL VALUES FROM HISTORY.
INSERT INTO PARCS_DB.dbo.tbl_Utilities_CUB_Totals
(Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
SELECT
Jan = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 2 THEN value END),
Feb = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 3 THEN value END),
Mar = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 4 THEN value END),
Apr = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 5 THEN value END),
May = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 6 THEN value END),
Jun = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 7 THEN value END),
Jul = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 8 THEN value END),
Aug = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 9 THEN value END),
Sep = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 10 THEN value END),
Oct = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 11 THEN value END),
Nov = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 12 THEN value END),
Dec = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 1 AND DATEPART(YEAR,DateTime) = DATEADD(YEAR, 1, @Date) THEN value END)
FROM RTPCUBHIST_001.runtime.dbo.History
WHERE Tagname IN ('FQI_DW_A_01.OldTotal','BOA001_STM_TOTAL.OldTotal','BOA002_STM_TOTAL.OldTotal')
AND wwVersion = 'Latest'
AND DateTime >= @Date
AND DateTime < DATEADD(YEAR, 1, @Date)
GROUP BY TagName;
-- GET CUB LIVE VALUES.
INSERT INTO PARCS_DB.dbo.tbl_Utilities_CUB_LiveValue
(Value)
SELECT
FROM RTPCUBHIST_001.runtime.dbo.v_live
WHERE Tagname IN ('BOA001_STM_TOTAL.Total','BOA002_STM_TOTAL.Total','FQI_DW_A_01.Total')
-- GET BLDG1 OLDTOTAL VALUES FROM HISTORY.
INSERT INTO PARCS_DB.dbo.tbl_Utilities_BLDG1_Totals
(Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
SELECT
Jan = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 2 THEN value END),
Feb = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 3 THEN value END),
Mar = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 4 THEN value END),
Apr = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 5 THEN value END),
May = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 6 THEN value END),
Jun = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 7 THEN value END),
Jul = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 8 THEN value END),
Aug = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 9 THEN value END),
Sep = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 10 THEN value END),
Oct = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 11 THEN value END),
Nov = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 12 THEN value END),
Dec = MAX(CASE WHEN DATEPART(MONTH, DateTime) = 1 AND DATEPART(YEAR,DateTime) = DATEADD(YEAR, 1, @Date) THEN value END)
FROM RTPBASHIST01.runtime.dbo.History
WHERE Tagname IN ('PROC_AIR_TOTAL.OldTotal','PLNT_STM_TOTAL.OldTotal','DOM_WTR_TOTAL.OldTotal','SAN_SEW_TOTAL.OldTotal')
AND wwVersion = 'Latest'
AND DateTime >= @Date
AND DateTime < DATEADD(YEAR, 1, @Date)
GROUP BY TagName;
-- GET BLDG1 LIVE VALUES
INSERT INTO PARCS_DB.dbo.tbl_Utilities_BLDG1_LiveValue
(Value)
SELECT
FROM RTPBASHIST01.runtime.dbo.v_live
WHERE Tagname IN ('DOM_WTR_TOTAL.Total','PLNT_STM_TOTAL.Total','PROC_AIR_TOTAL.Total','SAN_SEW_TOTAL.Total')
It's essentially the same piece of code 3 times, with the last 2 instances pulling in data from a linked server is the only difference. Everything else is copy/paste. Any help would be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Post the exact error you're getting.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mlwiller2 SSChasing Mays Points: 617 More actions · Answer 2

Msg 156, Level 15, State 1, Line 65

Incorrect syntax near the keyword 'FROM'.

Msg 156, Level 15, State 1, Line 95

Incorrect syntax near the keyword 'FROM'.

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

You don't specify any columns in your select statements.

INSERT INTO PARCS_DB.dbo.tbl_Utilities_CUB_LiveValue

(Value)

SELECT

FROM RTPCUBHIST_001.runtime.dbo.v_live

WHERE Tagname IN ('BOA001_STM_TOTAL.Total','BOA002_STM_TOTAL.Total','FQI_DW_A_01.Total')

The select portion needs to know what column(s) to use.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/