troubleshooting Question

SQL - Syntax Query

Avatar of Zack
ZackFlag for Australia asked on
Microsoft SQL ServerSQL
2 Comments1 Solution12 ViewsLast Modified:
Hi EE,

Yet again syntax issue:

IF OBJECT_ID(N'tempdb..#TempTableName') IS NOT NULL
BEGIN
DROP TABLE #TempTable
dECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)
declare @fromDate datetime
 
set @fromDate = '2017-4-1'


 
select * into #TempTable from
(SELECT  LEFT(CONVERT(VARCHAR, e.ACCOUNTINGDATE, 120), 7)  as TransDate, e.SUBLEDGERVOUCHERDATAAREAID as company, m.MAINACCOUNTID as ID,  
(a.ACCOUNTINGCURRENCYAMOUNT)AS BL
  FROM GENERALJOURNALACCOUNTENTRY a
  JOIN GENERALJOURNALENTRY e  ON e.[PARTITION] = a.[PARTITION] AND e.RECID = a.GENERALJOURNALENTRY
  join DIMENSIONATTRIBUTEVALUECOMBINATION davc on a.LEDGERDIMENSION = davc.RECID and e.[PARTITION] = davc.[PARTITION]
    join MAINACCOUNT m on davc.MAINACCOUNT = m.RECID and davc.[PARTITION] = m.[PARTITION]
  JOIN FISCALCALENDARPERIOD p ON p.[PARTITION] = e.[PARTITION] AND p.RECID = e.FISCALCALENDARPERIOD
  WHERE p.type =  1-- 0=Opening,1=Operating,2=Closing
 and e.accountingdate >= @FromDate and e.accountingdate < dateadd(month,12,@FromDate)
 )AS A
 
--select * from #TempTable
 
SELECT @cols = COALESCE(@cols + ',' ,'' ) + QUOTENAME(TransDate)
FROM #TempTable
GROUP BY QUOTENAME(TransDate)
Order by QUOTENAME(TransDate)
 
SELECT @query  = N'
SELECT * FROM #TempTable  PIVOT (SUM(BL) FOR TransDate
 IN (' + @cols +  N') ) AS pvt order by company'
 
EXEC sp_executesql @query;

Msg 102, Level 15, State 1, Line 33
Incorrect syntax near ';'.

Any assistance is appreciated.

Thank you. 

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros