Author |
Topic |
vishal.shah
Starting Member
4 Posts |
Posted - 2014-06-16 : 10:04:00
|
Hi there,I have a sql query below that returns an error that says " Incorrect syntax near DECLARE". Any ideas?ThanksDECLARE @RKDIVI VARCHAR(MAX), @itemgroup VARCHAR(MAX), @EAAITM VARCHAR(MAX), @RVYEA4 VARCHAR(MAX), @MONTH VARCHAR(MAX), @MM VARCHAR(MAX), @OB VARCHAR(MAX), @M13 VARCHAR(MAX), @SQL NVARCHAR(MAX);SET @RKDIVI = '000';SET @itemgroup = 'UKN';SET @EAAITM = '000';SET @RVYEA4 = '2014';SET @MONTH = '04';SET @MM = 'MM';SET @OB = 'OB';SET @M13 = 'M13';SET @SQL = 'Select * from openquery(M3EDBCFG,''SELECT v.RVYEA4, v.RKBIT1, v.RKBIT2, v.RKDIVI, NVL (d3.EAAITM, ''''' + @RKDIVI + ''''') AS DIM3, p.HierarchyIdentity2 AS DIM4, (CASE WHEN ''''' + @MONTH + ''''' = 0 THEN ''''' + @OB + ''''' WHEN ''''' + @MONTH + ''''' between 1 and 12 THEN to_char(to_date(''''' + @MONTH + ''''',''''' + @MM + '''''),''''' + @MM + ''''') ELSE ''''' + @M13 + ''''' END) as PERIOD, SUM (v.RVACAD + v.RVACAC) AS BALANCE FROM tagetik.tagetic_query v, COGNOS.PRODUCT_HIERARCHY4 p, (SELECT EAAITM FROM MVXJDTA.FCHACC WHERE EAAITP = 3) d3, (SELECT EAAITM FROM MVXJDTA.FCHACC WHERE EAAITP = 4) d4 WHERE v.RKBAKY = 44 AND d3.EAAITM(+) = v.RKBIT3 AND d4.EAAITM(+) = v.RKBIT4 AND p.ItemGroup(+) = NVL (d4.EAAITM, ''''' + @itemgroup + ''''') AND v.RVYEA4 = ''''' + @RVYEA4 + ''''' GROUP BY v.RVYEA4, v.RKBIT1, v.RKBIT2, v.RKDIVI, NVL (d3.EAAITM, ''''' + @RKDIVI + '''''), p.HierarchyIdentity2 '')';EXEC sp_executesql @SQL; |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-16 : 10:46:02
|
Parses fine with me. What do you have for code before the DECLARE statement? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
vishal.shah
Starting Member
4 Posts |
Posted - 2014-06-16 : 11:49:24
|
No other code before that. i think this is to do with the software im using that runs on sql. i have decided to try and a create a viea and the software can then refer to this view but i keep on getting an error saying incorrect syntax near 'SELECT'.Any ideas?CREATE VIEW dbo.TGK_VIEW AS SELECT * FROM openquery(M3EDBCFG,''SELECT v.RVYEA4, v.RKBIT1, v.RKBIT2, v.RKDIVI, NVL (d3.EAAITM, ''''' + @RKDIVI + ''''') AS DIM3, p.HierarchyIdentity2 AS DIM4, (CASE WHEN ''''' + @MONTH + ''''' = 0 THEN ''''' + @OB + ''''' WHEN ''''' + @MONTH + ''''' between 1 and 12 THEN to_char(to_date(''''' + @MONTH + ''''',''''' + @MM + '''''),''''' + @MM + ''''') ELSE ''''' + @M13 + ''''' END) as PERIOD, SUM (v.RVACAD + v.RVACAC) AS BALANCE FROM tagetik.tagetic_query v, COGNOS.PRODUCT_HIERARCHY4 p, (SELECT EAAITM FROM MVXJDTA.FCHACC WHERE EAAITP = 3) d3, (SELECT EAAITM FROM MVXJDTA.FCHACC WHERE EAAITP = 4) d4 WHERE v.RKBAKY = 44 AND d3.EAAITM(+) = v.RKBIT3 AND d4.EAAITM(+) = v.RKBIT4 AND p.ItemGroup(+) = NVL (d4.EAAITM, ''''' + @itemgroup + ''''') AND v.RVYEA4 = ''''' + @RVYEA4 + ''''' GROUP BY v.RVYEA4, v.RKBIT1, v.RKBIT2, v.RKDIVI, NVL (d3.EAAITM, ''''' + @RKDIVI + '''''), p.HierarchyIdentity2 ''); |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-16 : 13:44:01
|
Views cannot have parameters, nor variables. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|