Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with Syntax error Expand / Collapse
Author
Message
Posted Wednesday, April 23, 2014 12:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 28, 2014 6:46 AM
Points: 19, Visits: 37
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.
Post #1564432
Posted Wednesday, April 23, 2014 1:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 3,374, Visits: 7,296
Post the exact error you're getting.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1564436
Posted Wednesday, April 23, 2014 1:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 28, 2014 6:46 AM
Points: 19, Visits: 37
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'.
Post #1564438
Posted Wednesday, April 23, 2014 1:11 PM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 13,126, Visits: 11,964
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1564440
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse